?
Solved

quotation marks and variables in tsql

Posted on 2011-05-02
9
Medium Priority
?
625 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:rcttrmn
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35509786
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35509840
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35509843
And the big question (sorry for the multiple questions), why even use Dynamic SQL?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:rcttrmn
ID: 35509858
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
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 2000 total points
ID: 35509957
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
 

Author Comment

by:rcttrmn
ID: 35509983
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
 
LVL 4

Expert Comment

by:musalman
ID: 35510148
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35511993
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
 

Author Closing Comment

by:rcttrmn
ID: 35512944
This is definitely a type issue as opposed to playing the quotation marks game...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question