sql server 存储过程if分支太多 sql存储过程declare

 sqlserver 简单版存储过程declare和set

 

declare
@TimeNow datetime,  --当前时间
@StrAAA varchar(20),
@StrBBB varchar(20)
set @TimeNow=getDate()
set    @StrAAA='222'
set @StrBBB='333'
select @TimeNow,@StrAAA,@StrBBB


insert into WxTradePercent (TradeID,GoodsID,PtType,BID,UserID,
GiveToUserID,Value,TradePayment,PercentTage,PecentLevel,
Status,InsertTime,UpdateTime,DrawID) 
Values
('123',123,'goods',957,123,
456,30,40,null,3,
0,getdate(),null,null)

 

======================================================

--批量插入数据
declare
@TradeID nvarchar(50),
@GoodsID int,
@PtType varchar(20),
@BID int,
@UserID int,
@GiveToUserID int,
@Value decimal(18,2),
@TradePayment decimal(18,2),
@PercentTage decimal(18,2),
@PecentLevel int,
@Status int,
@InsertTime datetime,
@UpdateTime datetime,
@DrawID int
set @TradeID='123'
set @GoodsID=123
set @PtType='goods'
set @BID=957
set @UserID=123
set @GiveToUserID=456
set @Value=40
set @TradePayment=30
set @PercentTage=null
set @PecentLevel=2
set @Status=0
set @InsertTime=getdate()
set @UpdateTime=null
set @DrawID=null

insert into WxTradePercent 
(TradeID,GoodsID,PtType,BID,UserID,
GiveToUserID,Value,TradePayment,PercentTage,PecentLevel,
Status,InsertTime,UpdateTime,DrawID) 
select @TradeID,@GoodsID,@PtType,@BID,@UserID,
@GiveToUserID,@Value,@TradePayment,@PercentTage,@PecentLevel,
@Status,@InsertTime,@UpdateTime,@DrawID

 

========================================

 

insert into WxTradePercent 
(TradeID,GoodsID,PtType,BID,UserID,
GiveToUserID,Value,TradePayment,PercentTage,PecentLevel,
Status,InsertTime,UpdateTime,DrawID) 
select '123',123,'goods',957,123,
456,30,40,null,3,
0,getdate(),null,null

 

=======================================

 

public async Task<BaseResponse> AddListGoods(List<WxTradePercent> list)
        {
            var response = new BaseResponse();
            string currentMethodLog = "[AddListGoods()]提成记录，批量添加单品提成记录，";
            try
            {
                string sqlInsert = @" insert into WxTradePercent 
                                    (TradeID, GoodsID, PtType, BID, UserID,
                                    GiveToUserID, Value, TradePayment, PercentTage, PecentLevel,
                                    Status, InsertTime, UpdateTime, DrawID)
                                    select @TradeID, @GoodsID, @PtType, @BID, @UserID,
                                    @GiveToUserID, @Value, @TradePayment, @PercentTage, @PecentLevel,
                                    @Status, @InsertTime, @UpdateTime, @DrawID ";
                var sqlInsertInsert = await _dbBase.ExecuteAsync(sqlInsert, list);