Dynamic SQL - difference in handling of quotation marks with variables using EXEC

Question

Dynamic SQL - difference in handling of quotation marks with variables using EXEC

jshahan

SSCarpal Tunnel

Points: 4622
More actions
October 2, 2012 at 10:47 am

#282384

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'ClientID, ClientLastName, ClientFirstName, ClientCity'
SET @city = '''Springfield'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Clients WHERE ClientCity = ' + @city
EXEC (@sqlCommand)
Hello everyone
Forgive me for not posting data but I was hoping that it would not be necessary for this question since it doesn't appear to be data specific.
In order for the above query to run, the @columnlist variable requires a single set of quotes while the @city variable requires three sets.
Given that both variables are strings, I can't see why they require different handling and was hoping someone could explain this to me.
Thanks in advance for your time.
PS: I am aware this behavior does not occur with sp_executesql

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

What you are doing with the column list is building a list of columns that will be retrieved when the EXEC runs the sql command you build, and EXEC does NOT need these columns surrounded by quotes. On the other hand, the WHERE clause DOES need quotes, so you are doubling up quotes to "escape them".

This is what will end up in the variable @sqlcommand when the variable is passed as an argument to EXEC:

SELECT ClientID, ClientLastName, ClientFirstName, ClientCity FROM

Clients WHERE ClientCity = 'Springfield'

Notice that we don't need quotes around the columns but DO need quotes around the value you are testing for equality with in the WHERE clause.

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 2

Thanks, Patrick

Are you saying that the quotes function like escape characters and that handling of the escape characters is dependent on which clause they are in? EXEC wants one for SELECT but three for WHERE?

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 3

jshahan (10/2/2012)
Thanks, Patrick
Are you saying that the quotes function like escape characters and that handling of the escape characters is dependent on which clause they are in? EXEC wants one for SELECT but three for WHERE?

Yes!

Looking at the statement:

SET @city = '''Springfield'''

The first quote is of course needed because this tells the interpreter that we are starting a "string literal". Now the interpreter knows that we're building a string literal but sees the next quote. Now ordinarily this means that hey, we just ENDED the string literal, but within the interpreter it looks one character ahead, sees the third quote and instead of ENDING the string literal, the third quote "escapes" the second quote and makes it part of the character string we're building.

Thats my take on it anyways, ie., how I would do it if I were writing a T-SQL interpreter!

EDIT: The quote around SELECT in the line:

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Clients WHERE ClientCity = ' + @city

is not seen by the following EXEC, it however is used by the interpreter when building the SET statement, ie., we're building a string value to store into the variable @sqlCommand. The quotes around SELECT are not part of this value.

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

That makes sense to me. Actually makes enough sense that I can understand it instead of memorize it.

Thanks again.

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 5

jshahan (10/2/2012)
That makes sense to me. Actually makes enough sense that I can understand it instead of memorize it.
Thanks again.

You're welcome and good luck!

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 6

I thought of another way to say it that helps me. The triple quotes maintain a string as a string. The single ones become part of the literal text of the query. In this case, single quotes would cause @city to be treated as a column instead of a string.

Still it is weird that sp_executesql can get by with single quotes all the time...(as far as I know...)

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 7

jshahan (10/2/2012)
I thought of another way to say it that helps me. The triple quotes maintain a string as a string. The single ones become part of the literal text of the query. In this case, single quotes would cause @city to be treated as a column instead of a string.
Still it is weird that sp_executesql can get by with single quotes all the time...(as far as I know...)

I think thats a helpful way to think about it. Obviously test! I go through this escaping stuff with regular expressions etc so anything to get it done I'm for it!

With sp_executesql, I'm thinking you don't see the triple quotes because you'll pass the value "springfield" in as a parameter, so you use single quotes to store it into the parameter variable and no quotes need to be stored.