declare @sql as varchar(300)
, @FirstName as varchar(50)
, @Surname as varchar(50)
, @City as varchar(50)
, @County as varchar(50)
set @FirstName = 'myname'
set @Surname = 'mylastname'
set @City = null
set @County = 'county'
set @sql= 'SELECT Column_Names
FROM TableName ' +
case when rtrim(ltrim(isnull(@FirstName,'') + isnull(@Surname,'') + isnull(@City,'') + isnull(@County,''))) = '' then '' else 'where ' end
+ coalesce('FirstName LIKE ''%' + nullif(@FirstName,'')+ '%'' or ', '') +
+ coalesce('Surname LIKE ''%' + nullif(@Surname,'')+ '%'' or ', '') +
+ coalesce('City LIKE ''%' + nullif(@City,'')+ '%'' or ', '') +
+ coalesce('County LIKE ''%' + nullif(@County,'')+ '%''', '')
select @sql
@sql
的内容应该是:
SELECT Column_Names FROM TableName where FirstName LIKE '%myname%' or Surname LIKE %mylastname%' or County LIKE '%county%'
然后,要使用您创建的动态字符串,您需要执行Exec @Sql