CODEWITHSUNDEEP
sqlsql-server-2005
Andha
Andha

Reputation: 917

Using Single Quote in SQL Parameter

I'm wondering why these two following conditions in SQL return different results

First Condition
I write query as following :

DECLARE @idUser VARCHAR(MAX)
SELECT @idUser = COALESCE(@idUser+', ' , '') + CAST(idUser as VARCHAR(MAX))
FROM webuser
WHERE username IN ('John', 'Tom')

And it returns the user id for each usernames, below is the output given

1020, 1021


Second Condition
I write another different query. The username is already set as a parameter, and this is actually what I need, the username is set as parameter, below is the query used :

DECLARE @username VARCHAR(MAX)
SET @username = '''John'', ''Tom'''
DECLARE @idUser VARCHAR(MAX)
SELECT @idUser = COALESCE(@idUser+', ' , '') + CAST(idUser as VARCHAR(MAX))
FROM webuser
WHERE username IN (@username)

And it returns NULL value as the result.

I'm expecting the two queries return same output, I'm thinking that it's something about the quote in the @username, did I do it wrong or do you guys have any explanation and solution for this case ?

Upvotes: 0

Views: 4278

Answers (2)

cdonner
cdonner

Reputation: 37658

Your second condition tries to find a single user name "'John', 'Tom'" that does not exist. In order to use this string in your query the way you intend to, you need dynamic SQL, or you could use LIKE, as in:

DECLARE @username VARCHAR(MAX) 
SET @username = '!John, !Tom' 
DECLARE @idUser VARCHAR(MAX) 
SELECT @idUser = COALESCE(@idUser+', ' , '') + CAST(idUser as VARCHAR(MAX)) 
FROM webuser 
WHERE @username LIKE '%!'+username+'%'

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

@username is a single string variable, not an array of strings. So your query where you replace the variable is looking for a single username in the table with the value 'John', 'Tom', not two separate values 'John' and 'Tom'... SQL Server doesn't know what an array is, and can't transform that string into multiple values for you.

You will need to use dynamic SQL or split the values first.

Dynamic SQL:

DECLARE 
    @s VARCHAR(MAX),
    @username VARCHAR(MAX);

SELECT 
    @s = '', 
    @username = '''John'', ''Tom''';

DECLARE @sql NVARCHAR(MAX);
SELECT @sql  = N'SELECT @s = @s + '',''
    + CONVERT(VARCHAR(MAX), idUser) 
  FROM dbo.webuser
  WHERE username IN (' + @username + ');';

EXEC sp_executesql 
    @sql, 
    N'@s VARCHAR(MAX) OUTPUT', 
    @s = @s OUTPUT;

PRINT STUFF(@s, 1, 1, '');

Some ideas for splitting here: Passing a varchar full of comma delimited values to a SQL Server IN function

Upvotes: 2

Related Questions