Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@pchrTest1

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @pchrTest1

This codes seems it does not work.

Thanks in advance

henlylow
Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf
miketravers
You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.

Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@pchrTest1
AS

EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @pchrTest1)

Regards,

-chris
khurrammughal
You haven't specified a datatype for the parameter.
Try: @pchrTest1 varchar(256)

It does work like this (without dynamic SQL).
Passing parameter in LIKE statement with '%'