在sp中添加下面的红色部分，就是执行sp时的返回类型，后面在EF中添加该sp后，EF会在DBContext文件中，自动生成调用该sp的代码，包括返回类型等，如下：
public virtual ObjectResult<SP_CustomerRequestWriteoff_Result> SP_CustomerRequestWriteoff(Nullable<int> qrTransID, Nullable<int> campaignID, string openID, string buyList, string giftList)
{ var qrTransIDParameter = qrTransID.HasValue ? new ObjectParameter("qrTransID", qrTransID) : new ObjectParameter("qrTransID", typeof(int)); var campaignIDParameter = campaignID.HasValue ? new ObjectParameter("campaignID", campaignID) : new ObjectParameter("campaignID", typeof(int)); var openIDParameter = openID != null ? new ObjectParameter("openID", openID) : new ObjectParameter("openID", typeof(string)); var buyListParameter = buyList != null ? new ObjectParameter("buyList", buyList) : new ObjectParameter("buyList", typeof(string)); var giftListParameter = giftList != null ? new ObjectParameter("giftList", giftList) : new ObjectParameter("giftList", typeof(string)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SP_CustomerRequestWriteoff_Result>("SP_CustomerRequestWriteoff", qrTransIDParameter, campaignIDParameter, openIDParameter, buyListParameter, giftListParameter); }然后直接调用即可，如下：
public static Result ExportPeriodDetailReport(DateTime? beginDate, DateTime? endDate, string activityId, string storeCode, string flag)
{ using (var db = new SAISC_4S_PotentialEntities()) { //调用存储过程 if (flag == "1") { var query = db.sp_Rpt_Act_Weekly_List(beginDate, endDate, activityId, storeCode).ToList(); return new Result(true, "", query); } } }
USE [FrisoFidpDB]
GO/****** Object: StoredProcedure [dbo].[SP_CustomerRequestWriteoff] Script Date: 12/26/2016 14:45:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[SP_CustomerRequestWriteoff] @qrTransID INT , @campaignID INT , @openID NVARCHAR , --购买明细列表，格式：商品编号:数量,多个以英文逗号隔开，如51234:2,34532:1,9876:3 @buyList NVARCHAR , --赠品明细列表，格式：商品编号:数量,多个以英文逗号隔开，如51234:2,34532:1,9876:3 @giftList NVARCHARAS BEGIN IF ( @qrTransID = NULL OR @qrTransID = 0 ) BEGIN DECLARE @sp_Result TABLE ( errCode INT , errMsg NVARCHAR(200) , proCode NVARCHAR(50) ) SELECT * FROM @sp_Result RETURN END DECLARE @errcode INT , @errmsg NVARCHAR(500) , @giftCount INT , @idx INT , @customerID INT , @qty INT , @stockType INT , @shopCode NVARCHAR(50) , @cityID INT , @proCode NVARCHAR(50) , @stockQty INT , @giftName NVARCHAR(50) , @fmcCode NVARCHAR(50) , @distributorCode NVARCHAR(200) , @writeOffUserCode NVARCHAR(50) , @writeOffTime DATETIME DECLARE @table TABLE ( ID INT , ProCode NVARCHAR(200) , ProCount NVARCHAR(50) , Qty INT , Flag INT ) SET @errcode = 0; SELECT @shopCode = ShopCode , @cityID = SalesCityId , @fmcCode = GenUserCode , @writeOffTime = WriteOffTime , @writeOffUserCode = WriteOffUserCode FROM dbo.IDreamTransaction WHERE id = @qrTransID SELECT TOP 1 @customerID = Id FROM dbo.Customer WHERE OpenId = @openID --核销条件检查 IF ( LEN(@buyList) = 0 OR LEN(@giftList) = 0 ) BEGIN SET @errcode = 1000; SET @errmsg = '购买明细和赠品明细不能为空'; END IF ( @writeOffTime != NULL AND @writeOffUserCode != NULL ) BEGIN SET @errcode = 9000; SET @errmsg = '此二维码已被使用过'; END --设置当前会话的数据库锁超时时间为20秒 SET LOCK_TIMEOUT 20000 BEGIN TRY BEGIN TRAN trans --赠品库存检查 IF ( @errcode = 0 ) BEGIN --购买明细 INSERT INTO @table ( ID , ProCode , ProCount , Flag ) SELECT ID , [key] , value , 1 FROM dbo.[Fun_GetKeyValueList](@buyList, ',', ':') --赠品明细 INSERT INTO @table ( ID , ProCode , ProCount , Flag ) SELECT ID , [key] , value , 2 FROM dbo.[Fun_GetKeyValueList](@giftList, ',', ':') UPDATE @table SET Qty = CAST(ProCount AS INT) WHERE ISNUMERIC(ProCount) = 1 UPDATE @table SET Qty = 0 WHERE ISNUMERIC(ProCount) = 0 SELECT @giftCount = COUNT(*) FROM @table WHERE Flag = 2 IF ( @giftCount > 0 ) BEGIN SET @idx = 1; WHILE ( @idx <= @giftCount ) BEGIN SELECT @proCode = ProCode , @qty = Qty FROM @table WHERE ID = @idx IF ( @qty > 0 ) BEGIN SELECT @stockType = m.StockType , @giftName = n.NameCN FROM dbo.CampaignGift m LEFT JOIN dbo.Product n ON m.GiftCode = n.Code WHERE m.CampaignId = @campaignID AND m.GiftCode = @proCode --库存类型，1-门店库存 2-城市库存 3-全国库存 IF ( @stockType = 1 ) BEGIN SELECT @stockQty = ISNULL(StockQty, 0) FROM dbo.IDreamStock WITH ( UPDLOCK ) WHERE CampaignId = @campaignID AND GiftCode = @proCode AND ShopCode = @shopCode AND SalesCityId = @cityID END ELSE IF ( @stockType = 2 ) BEGIN SELECT @stockQty = ISNULL(StockQty, 0) FROM dbo.IDreamStock WITH ( UPDLOCK ) WHERE CampaignId = @campaignID AND GiftCode = @proCode AND SalesCityId = @cityID END ELSE IF ( @stockType = 3 ) BEGIN SELECT @stockQty = ISNULL(StockQty, 0) FROM dbo.IDreamStock WITH ( UPDLOCK ) WHERE CampaignId = @campaignID AND GiftCode = @proCode END --库存不足直接返回调用方 IF ( @qty > @stockQty ) BEGIN SET @errcode = 2000; SET @errmsg = '赠品' + @giftName + '库存不足'; BREAK; END END SET @idx += 1; END END END --核销操作 IF ( @errcode = 0 ) BEGIN --添加核销流水并更新库存 BEGIN TRY SET @giftCount = 0; SELECT @giftCount = COUNT(*) FROM @table WHERE Flag = 2 IF ( @giftCount > 0 ) BEGIN SET @idx = 1; WHILE ( @idx <= @giftCount ) BEGIN SELECT @proCode = ProCode , @qty = Qty FROM @table WHERE Flag = 2 AND ID = @idx SELECT @stockType = m.StockType , @giftName = n.NameCN FROM dbo.CampaignGift m LEFT JOIN dbo.Product n ON m.GiftCode = n.Code WHERE m.CampaignId = @campaignID AND m.GiftCode = @proCode --更新库存，库存类型，1-门店库存 2-城市库存 3-全国库存 IF ( @stockType = 1 ) BEGIN UPDATE IDreamStock SET StockQty = StockQty - @qty WHERE CampaignId = @campaignID AND GiftCode = @proCode AND ShopCode = @shopCode AND SalesCityId = @cityID SELECT TOP 1 @distributorCode = DistributorCode FROM dbo.IDreamStock WHERE CampaignId = @campaignID AND GiftCode = @proCode AND ShopCode = @shopCode AND SalesCityId = @cityID END ELSE IF ( @stockType = 2 ) BEGIN UPDATE IDreamStock SET StockQty = StockQty - @qty WHERE CampaignId = @campaignID AND GiftCode = @proCode AND SalesCityId = @cityID SELECT TOP 1 @distributorCode = DistributorCode FROM dbo.IDreamStock WHERE CampaignId = @campaignID AND GiftCode = @proCode AND SalesCityId = @cityID END ELSE IF ( @stockType = 3 ) BEGIN UPDATE IDreamStock SET StockQty = StockQty - @qty WHERE CampaignId = @campaignID AND GiftCode = @proCode SELECT TOP 1 @distributorCode = DistributorCode FROM dbo.IDreamStock WHERE CampaignId = @campaignID AND GiftCode = @proCode END --添加核销流水 IF ( @qty > 0 ) BEGIN INSERT INTO dbo.IDreamWriteOff ( TransactionId , AppId , CampaignId , SalesCityId , GiftCode , GiftQuantity , ShopCode , FMCCode , CustomerId , OpenId , Remark , CreateTime , UpdateTime , DistributorCode , WriteOffTime , WriteOffType ) SELECT @qrTransID , 0 , @campaignID , @cityID , @proCode , @qty , @shopCode , @fmcCode , ISNULL(@customerID, 0) , @openID , 'idream核销' , GETDATE() , GETDATE() , @distributorCode , GETDATE() , '线上兑换' END SET @idx += 1; END END END TRY BEGIN CATCH SET @errcode = 3000; SET @errmsg = '添加核销流水并更新库存时异常，信息为' + ERROR_MESSAGE() END CATCH --添加购买记录 IF ( @errcode = 0 ) BEGIN TRY SET @giftCount = 0; SELECT @giftCount = COUNT(*) FROM @table WHERE Flag = 1 IF ( @giftCount > 0 ) BEGIN SET @idx = 1; WHILE ( @idx <= @giftCount ) BEGIN SELECT @proCode = ProCode , @qty = Qty FROM @table WHERE Flag = 1 AND ID = @idx SELECT TOP 1 @distributorCode = DistributorCode FROM dbo.IDreamStock WHERE CampaignId = @campaignID AND GiftCode = @proCode IF ( @qty > 0 ) BEGIN INSERT INTO dbo.IDreamBuyDetail ( TransactionId , ProductCode , Qty , Remark , CreateTime ) SELECT @qrTransID , @proCode , @qty , 'idream核销' , GETDATE() END SET @idx += 1; END END END TRY BEGIN CATCH SET @errcode = 4000; SET @errmsg = '添加购买记录发生异常，信息为' + ERROR_MESSAGE() END CATCH --更新二维码事物表 IF ( @errcode = 0 ) BEGIN TRY UPDATE dbo.IDreamTransaction SET WriteOffTime = GETDATE() , WriteOffUserCode = @openID , UpdateTime = GETDATE() , ExpireTime = GETDATE() + 10 WHERE Id = @qrTransID; END TRY BEGIN CATCH SET @errcode = 5000; SET @errmsg = '添加购买记录发生异常，信息为' + ERROR_MESSAGE() END CATCH END IF ( @errcode = 0 ) BEGIN COMMIT TRAN trans END ELSE BEGIN ROLLBACK TRAN trans SET @errcode = 8000; SET @errmsg = '核销过程中异常，操作回滚'; END END TRY BEGIN CATCH ROLLBACK TRAN trans SET @errcode = 6000; SET @errmsg = '核销过程中异常，信息为' + ERROR_MESSAGE(); END CATCH SELECT @errcode AS errCode , @errmsg AS errMsg , @proCode AS proCode; RETURN 1; END