asked on

quotation marks and variables in tsql

How many quotation marks are needed to surround a variable in a dynamic SQL statement. I may be dealing with a type issue but I don't think so. If I run the following sql (this is a reduced version but the structure is the same), I get the following error: The thread '(local) [54]' (0x1254) has exited with code 0 (0x0). The field "Processed" is a type "tinyint". Also, no matter where I try to call @p I get the same error. For instance if I remove the sub-query and add it as a field anywhere in the query it chokes as well.

DECLARE @p tinyint
SET @p = 1
SELECT @SQL2 = '
INSERT INTO Table1(
Field1, Field2,' + @Cols +'
)
SELECT Field1, Field2 = ‘’ + @p + ’’, ' + @Cols +'
FROM Table1 A JOIN (SELECT fieldStart = DATEADD(day,7,[PeriodStart]), FROM Table1 WHERE Processed = '' + @p + '') C ON A.Id = C.Id

Thanks
Bob

Anthony Perkins

See if this works for you:

DECLARE @p tinyint
SET @p = 1
SELECT @SQL2 = '
INSERT INTO Table1(
Field1, Field2,' + @Cols + ')
SELECT      Field1, Field2 = ' + @p + ''''  + @Cols +'
 FROM Table1 A JOIN  (SELECT fieldStart = DATEADD(day,7,[PeriodStart]), FROM Table1 WHERE Processed = ''' + @p + ''') C ON A.Id = C.Id'

Open in new window

Anthony Perkins

It occurs to me that cannot be right. There are opening quotes around @Cols but no closing quotes. Perhaps if you could show us what you are trying to achieve.

Anthony Perkins

And the big question (sorry for the multiple questions), why even use Dynamic SQL?

rcttrmn

ASKER

That did not work. I don't think it's a matter of the quotation marks I think it is a type issue. I need to insert the variable @p which is a tinyint as into the @sql string. I simplified the query to illustrate. We can ignore the additional @Cols variable for this example.

DECLARE @p tinyint
DECLARE @SQL2 varchar(max)
SET @p = 1
SELECT @SQL2 = '
INSERT INTO Table1(
Field1, Field2
)
SELECT Field1, Field2 = ‘’ + @p + ’’
FROM Table1

ASKER CERTIFIED SOLUTION

Dale Burrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rcttrmn

ASKER

Thanks Dale. Actually the big picture is that I want to execute the insert statement in a while loop. But I was having a hard time getting the most basic sql statement to accept the variable. Converting worked, cast also worked. I don't have a good example worked out yet for the loop but I will in the a.m..

musalman

For Convenience and Time Saving Now, You can use Temporary Table.
Benefit is that , you can put all manipulated values using Insert/Update Logic.
Then finally Insert the data into Actual table without Looping,,,

This is Just a Thought !!!

Alpesh Patel

DECLARE @p tinyint
SET @p = 1
SELECT @SQL2 = '
INSERT INTO Table1(
Field1, Field2,' + @Cols +'
)
SELECT Field1, Field2 = ''' + @p + ''', ''' + @Cols +'''
FROM Table1 A JOIN (SELECT fieldStart = DATEADD(day,7,[PeriodStart]), FROM Table1 WHERE Processed = ''' + @p + ''') C ON A.Id = C.Id'

rcttrmn

ASKER

This is definitely a type issue as opposed to playing the quotation marks game...