sql server 中存储过程where条件需要判断进行拼接
时间：2021-07-01 10:21:17
帮助过：5人阅读
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条件需要判断进行拼接
标签：
