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
rcttrmnAsked:
Who is Participating?
 
Dale BurrellDirectorCommented:
I need to see the bigger picture to know what you are actually trying to do... this code works, but I don't know if it does what you are looking for.
declare @Cols nvarchar(max), @sql2 nvarchar(max)
set @Cols = 'temp1, temp2'

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

select @SQL2

Open in new window

0
 
Anthony PerkinsCommented:
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

0
 
Anthony PerkinsCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
And the big question (sorry for the multiple questions), why even use Dynamic SQL?
0
 
rcttrmnAuthor Commented:
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
0
 
rcttrmnAuthor Commented:
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..
0
 
musalmanERP ConsultantCommented:
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 !!!
0
 
Alpesh PatelAssistant ConsultantCommented:
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'
0
 
rcttrmnAuthor Commented:
This is definitely a type issue as opposed to playing the quotation marks game...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.