存储过程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

 

转载于:https://www.cnblogs.com/kevin1988/p/3507618.html