Geek asks and answers

How to use LIKE in a dynamic t-sql expression in a stored procedure?

I am trying to use the LIKE keyword with% wildcards that carry this parameter, but I'm not sure how to get% characters in the statement without breaking it. Right now I have:

SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE %@search%'

I get a SqlException error in my .net application that says “Invalid syntax next to“ @search ”when I run it. The error disappears if I delete% of the characters surrounding the @search parameter.

+3
source share
5 answers

The% characters must be in the search bar ...

SET @search = '%' + @search + '%'
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE @search'

Please note that the following will work, but introduces potential for SQL injection vulnerability ...

-- DON'T do this!
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ''%' + @search + '%'''
+20
source 
SET @SQLQuery = 'SELECT * from [tblApps] WHERE [firstName] LIKE ''%'' + @search + ''%'''
exec sp_executesql @query=@SQLQuery, @params=N'@search nvarchar(96)', @search=@search

, sql , SQL-.

+3
SET @search = '%' + @search 
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ' + @search + '%'
+1
source

It worked for me!

SET @search = '''%' + @search + '%'''
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE' + @search
EXEC sp_executesql @SQLQuery
0
source 
declare @Cmd nvarchar(2000)
declare @eName varchar(10)
set @eName='a'
set @Cmd= 'select * from customer1 where name LIKE '''+'%' +@eName+ '%' + ''''
print @Cmd
EXECUTE sp_executesql @Cmd
0
source

More articles:


All Articles