October 2, 2012 at 10:47 am
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
October 2, 2012 at 11:49 am
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.
October 2, 2012 at 12:28 pm
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?
October 2, 2012 at 12:38 pm
jshahan (10/2/2012)
Thanks, PatrickAre 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.
October 2, 2012 at 12:42 pm
That makes sense to me. Actually makes enough sense that I can understand it instead of memorize it.
Thanks again.
October 2, 2012 at 12:47 pm
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!
October 2, 2012 at 12:50 pm
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...)
October 2, 2012 at 1:10 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply