CODEWITHSUNDEEP
sqlsql-server-2008t-sql
David
David

Reputation: 1041

How to Parameterize a query with Like operator

I am trying to retrieve some data with below query and i Want to parameterize this.

select Col A,Col B,Col C,Col D
From TableA
where (COL A like '01%' or COL A like '02%')

I am using below query but i am not getting the output.

declare @p1 varchar ='01%',
        @p2 varchar ='02%'
select Col A,Col B,Col C,Col D
From TableA
where (COL A like @p1 or COL A like @p2)

Please suggest better way how to parameterize above query using sql server 2008r2.

Thanks

Upvotes: 0

Views: 240

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

The problem is that you did not specify a length for your parameters, and they are being resolved as varchar(1). You could see this with debugging 101, e.g.

DECLARE @p1 VARCHAR = '01%';
SELECT p1 = @p1, lenp1 = DATALENGTH(@p1);

Result:

p1   lenp1
---- -----
0    1

Try:

DECLARE @p1 VARCHAR(32) = '01%',
        @p2 VARCHAR(32) = '02%';
...

Please be explicit instead of lazy. Also use semi-colons to terminate statements.

Upvotes: 4

Related Questions