sql server 中存储过程where条件需要判断进行拼接

USE [ Mct ] 2 GO 3 /* ***** Object: StoredProcedure [dbo].[P_CountIcCardTradInfo] Script Date: 05/20/2015 09:04:45 ***** */ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 9 ALTER PROCEDURE [ dbo ] . [ P_CountIcCardTradInfo ] 10 ( 11 @bdate datetime , 12 @edate datetime , 13 @CusName varchar ( 30 ), 14 @CardCode varchar ( 30 ), 15 @BusinessType varchar ( 30 ), 16 @UserCode varchar ( 30 ) 17 ) 18 AS 19 Declare @strSql varchar ( max ) 20 set @strSql = ‘ select RecordTime,CardCode,CusCode,RelName,BoothCode,Deposit,UserCode,BusinessType,BusinessValue from #TempTableTradInfoCount where 1=1 ‘ ; 21 22 BEGIN 23 -- 创建临时表用于保存数据 24 Create table #TempTableTradInfoCount 25 ( 26 27 RecordTime datetime not null , 28 CardCode varchar ( 10 ) not null , 29 CusCode varchar ( 30 ) not null , 30 RelName varchar ( 30 ) not null , 31 BoothCode varchar ( 30 ) not null , 32 Deposit decimal ( 10 , 2 ) not null , 33 UserCode varchar ( 30 ) not null , 34 BusinessType varchar ( 50 ) not null , 35 BusinessValue varchar ( 50 ) not null 36 ) 37 38 if ( @bdate is not null ) and ( @bdate != ‘‘ ) 39 set @strSql = @strSql + ‘ and CONVERT(varchar(10),RecordTime,120) >= ‘‘‘ + CONVERT ( varchar ( 10 ), @bdate , 120 ) + ‘‘‘‘ ; 40 41 if ( @edate is not null ) and ( @edate != ‘‘ ) 42 set @strSql = @strSql + ‘ and CONVERT(varchar(10),RecordTime,120) <= ‘‘‘ + CONVERT ( varchar ( 10 ), @edate , 120 ) + ‘‘‘‘ ; 43 44 if ( @CusName is not null ) and ( @CusName != ‘‘ ) 45 set @strSql = @strSql + ‘ and RelName = ‘‘‘ + @CusName + ‘‘‘‘ ; 46 47 if ( @CardCode is not null ) and ( @CardCode != ‘‘ ) 48 set @strSql = @strSql + ‘ and CardCode = ‘‘‘ + @CardCode + ‘‘‘‘ ; 49 50 if ( @BusinessType is not null ) and ( @BusinessType != ‘‘ ) 51 set @strSql = @strSql + ‘ and BusinessType = ‘‘‘ + @BusinessType + ‘‘‘‘ ; 52 53 if ( @UserCode is not null ) and ( @UserCode != ‘‘ ) 54 set @strSql = @strSql + ‘ and UserCode = ‘‘‘ + @UserCode + ‘‘‘‘ ; 55 56 -- print @strsql; 57 58 -- 主卡 59 insert into #TempTableTradInfoCount(RecordTime,CardCode,CusCode,RelName,BoothCode,Deposit,UserCode,BusinessType,BusinessValue) 60 select EditDate,IcCardNoCpu,Kh_dm,Kh_mc, ‘‘ , 0 ,UseCode, ‘ MainCard ‘ , ‘ 主卡 ‘ from Khda where IcCardNoCpu != ‘‘ and (UseCode != ‘ Y ‘ or UseCode != ‘ y ‘ ) 61 62 -- 副卡 63 insert into #TempTableTradInfoCount(RecordTime,CardCode,CusCode,RelName,BoothCode,Deposit,UserCode,BusinessType,BusinessValue) 64 select EditDate,PartCardNo,Khdm,Khmc, ‘‘ ,ysje,UserCode, ‘ ViceCard ‘ , ‘ 副卡 ‘ from IcCardCopyRecord 65 66 -- 临时卡转正 67 insert into #TempTableTradInfoCount(RecordTime,CardCode,CusCode,RelName,BoothCode,Deposit,UserCode,BusinessType,BusinessValue) 68 select pd.RecordTime,cus.IcCardNoCpu,cus.Kh_dm,cus.Kh_mc, ‘‘ ,pd.Deposit,Registration, ‘ TemCard ‘ , ‘ 临时卡 ‘ from PositiveDeposit pd inner join Khda cus on pd.CustomerCord = cus.Kh_dm 69 70 -- 挂失 71 insert into #TempTableTradInfoCount(RecordTime,CardCode,CusCode,RelName,BoothCode,Deposit,UserCode,BusinessType,BusinessValue) 72 select LossRq,CardCode,Khdm,Khmc, ‘‘ ,Gsf,UserCode, ‘ LossCard ‘ , ‘ 挂失 ‘ from IcCardLoss 73 74 -- 退卡 75 insert into #TempTableTradInfoCount(RecordTime,CardCode,CusCode,RelName,BoothCode,Deposit,UserCode,BusinessType,BusinessValue) 76 select EditDate,CardCode,Khdm,Khmc, ‘‘ ,Tkf,UserCode, ‘ BackCard ‘ , ‘ 退卡 ‘ from IcCardReturn 77 78 exec ( @strSql ); 79 80 END

 

sql server 中存储过程where条件需要判断进行拼接

标签：

查看更多关于sql server 中存储过程where条件需要判断进行拼接的详细内容...