存储过程like参数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[Proc_QueryFeeInfoWithLike]
@feedict varchar(20),
@idnum varchar(20),
@name varchar(50),
@address varchar(100),
@payFlag int
as
begin
SET NOCOUNT ON;
declare @sql nvarchar(4000)
declare @ParameterDefinition nvarchar(4000);
select @ParameterDefinition=\’@nameParameter varchar(50),@idnumParameter varchar(20),@addressParameter varchar(100),@feedictParameter varchar(20),@payflagParameter int\’;
select @sql=N\’select a.id,a.IdNum,b.namecn,b.phonenum,b.addresscn,c.FeeDictName,a.amount,a.BelatedNum
from charges a
left join Resident b
on a.idnum=b.IdNum
left join feeDict c
on a.feedict=c.id
where a.payflag=@payflagParameter\’
if @name is not null
select @sql=@sql+N\’ and b.namecn like \’\’%\’\’\’+\’+@nameParameter+\’+\’\’\’%\’\’\’;
if @idnum is not null
select @sql=@sql+N\’ and a.idnum like \’\’%\’\’\’+\’+@idnumParameter+\’+\’\’\’%\’\’\’;
if @address is not null
select @sql=@sql+N\’ and b.addresscn like \’\’%\’\’\’+\’+@addressParameter+\’+\’\’\’%\’\’\’;
if @feedict is not null
select @sql=@sql+N\’ and a.feedict=@feedictParameter\’;
exec sp_executeSQL @sql,@ParameterDefinition,@nameParameter=@name,@idnumParameter=@idnum,@addressParameter=@address,@feedictParameter=@feedict,@payflagParameter=@payFlag
end