CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Industrial Themes
Industrial Themes

Reputation: 567

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

I'm trying to use the LIKE keyword with the % wildcards wrapping the parameter, but I'm not sure how to get the % characters into 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 app that says "Incorrect syntax near '@search' when I run it. The error goes away if I remove the % characters surrounding the @search parameter.

Upvotes: 8

Views: 42990

Answers (5)

sohan yadav
sohan yadav

Reputation: 69

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

Upvotes: -2

Av Pinzur
Av Pinzur

Reputation: 2228

The % characters have to be in the search string...

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

Note that the following would also work, but introduces potential for a SQL injection vulnerability...

-- DON'T do this!
SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE ''%' + @search + '%'''

Upvotes: 34

VPP
VPP

Reputation: 789

This worked for me!

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

Upvotes: -2

hova
hova

Reputation: 2841

SET @SQLQuery = 'SELECT * from [tblApps] WHERE [firstName] LIKE ''%'' + @search + ''%'''
exec sp_executesql @query=@SQLQuery, @params=N'@search nvarchar(96)', @search=@search

The only difference from this version as compared to the others is that the dynamic execution of the sql is in fact parameterized which mitigates sql injection a bit.

Upvotes: 10

Neil Knight
Neil Knight

Reputation: 48587

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

Upvotes: 1

Related Questions