Using Single Quote in SQL Parameter

Question

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 ?

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

@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

edited May 23 '17 at 12:03

Community

1
1

answered Jan 26 '12 at 04:25

Aaron Bertrand

272,866
37
466
490

I tried your query and got an error message : `Incorrect syntax near '='.`. I think the error occurred somewhere at the `@s = @s OUTPUT;` line. – Andha Jan 26 '12 at 04:59
Can you post the exact query you're running in its entirety, and the complete error message (including line number), as an edit to your question? My guess is that you've mismatched some apostrophes. – Aaron Bertrand Jan 26 '12 at 05:03
since it's returning error, I had to use SET to set the value of each parameters to make it work. eg : `DECLARE @s VARCHAR(MAX)` then I set the value `SET @s = ''`. The query's fine except the error I mentioned before, then I tried to comment the stored procedure part and it's returning no error. So I assume the cause is at the line as I posted before. Here is the complete error message `Msg 102, Level 15, State 1, Line 1` `Incorrect syntax near '='.` – Andha Jan 26 '12 at 06:33
Is it possible you're using SQL Server 2005? You shouldn't need to `DECLARE` and `SET` separately on 2008 and above. If you're on SQL Server 2005, please re-tag your question. – Aaron Bertrand Jan 26 '12 at 06:36
Ya, sorry for that. I'm using the SQL Server Management Studio for SQL Server 2008 R2. But the database is actually running under the SQL Server 2005. Thanks for that correction. So the error that I got is possibly caused by the SQL Server version ? – Andha Jan 26 '12 at 06:45
Yes, inline assignment for `DECLARE` only works in SQL Server 2005 and up. Updated answer. – Aaron Bertrand Jan 26 '12 at 14:29
Since there's still one declaration which value needs to be set, so I set the value for the @sql. But it still couldn't work and returned the same error message. Is it something with the `+` in the `N'SELECT @s += '',''` – Andha Jan 27 '12 at 02:34
Again I was fooled by your tag of the question as sql-2008 not sql-2005. Try now please. – Aaron Bertrand Jan 28 '12 at 15:10
thanks a lot for your help. it's now working with a bit revision. thanks so much ! – Andha Jan 30 '12 at 06:31

cdonner · Answer 2 · 2012-02-09T20:15:37.703

1

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+'%'

edited Feb 09 '12 at 20:15

answered Jan 26 '12 at 04:17

cdonner

37,019
22
105
153

Ah, I see. Do you have any tutorial or article references for dynamic SQL ? I ain't too familiar with it. Thx. – Andha Jan 26 '12 at 04:21
See Aaron's response. If you use LIKE the way I showed you, you don't need dynamic SQL. – cdonner Jan 26 '12 at 04:27
I've tried using the LIKE and added an `@` before the `username` but it's still returning NULL value. – Andha Jan 26 '12 at 04:35
1

Your like trick won't work exactly as written. Consider a username in the table like 'oh' or 'to' or 'om' - those will match the `LIKE` pattern but aren't actual matches. – Aaron Bertrand Jan 26 '12 at 06:37

Using Single Quote in SQL Parameter

2 Answers2