SET @whereCond = @whereCond + ' AND name LIKE ''%'' + @name + ''%'''
Is there something wrong here? After I generate where condition, I execute it with sp_executesql
, but I did get anything. When I SELECT the same thing without sp, it's ok.
How to use LIKE in sp_executesql? Can you bring some examples, please?
Thank you.
UPDATE
declare @name nvarchar(50)
set @name = 'a'
SELECT *
FROM Tbl_Persons WHERE 1 = 1 AND lastname LIKE '%a%'
exec sp_executesql
N'SELECT *
FROM Tbl_Persons WHERE 1 = 1 AND lastname LIKE ''%@name%''',
N'@name nvarchar(50)',
@name=@name
First query returns values, second one doesn't return anything.
What's the difference?
The following works for me
declare @name varchar(50)
set @name = 'WAITFOR'
exec sp_executesql
N'select * from sys.messages WHERE text LIKE ''%'' + @name + ''%''',
N'@name varchar(50)',
@name=@name
I think the problem must lie elsewhere.
Edit: Following your update you need to use
exec sp_executesql
N'SET @name = ''%'' + @name + ''%'';
SELECT *
FROM Tbl_Persons WHERE 1 = 1 AND lastname LIKE @name',
N'@name nvarchar(50)',
@name=@name
As it stands you are searching for text containing the actual substring @name