Executing multiple query strings using a variable declared at first one

Question

I need to execute a long dynamic query string based on a xml variable, then I needed to split the string on multiple varchar variables, each one containing part of an update in a table inside a cursor.

I will post part of the query:

DECLARE @QUERY1 VARCHAR(8000);
DECLARE @QUERY2 VARCHAR(8000);
DECLARE @QUERY3 VARCHAR(8000);
DECLARE @QUERY4 VARCHAR(8000);

DECLARE @cols AS VARCHAR(8000);

DECLARE @begin INT
DECLARE @totalid INT
DECLARE @groupsize INT
SET @groupsize = 0
SELECT @totalid = MAX(IntegrationId) FROM Integration
set @QUERY1 = N'
BEGIN TRANSACTION
DECLARE @xml XML 
DECLARE @XMLReceiverID int
DECLARE XMLRowItem CURSOR FOR
SELECT XMLReceiverID,XMLContent FROM XMLReceiver WHERE FlagImportData = 0 and Typology = ''XXXX''
OPEN XMLRowItem
FETCH XMLRowItem INTO @XMLReceiverID,@xml
WHILE @@Fetch_Status = 0

BEGIN
';
set @QUERY2 = N'
INSERT INTO IntegrationData (XmlReceiverID) 
';
set @QUERY3 = N'
SELECT @XMLReceiverID
';      
--PRINT @QUERY1 + @QUERY2 + @QUERY3
EXEC (@QUERY1 + @QUERY2 + @QUERY3)      
WHILE @groupsize <= @totalid
BEGIN
SET @begin = @groupsize
SET @groupsize = @groupsize + 10
select @cols = 
(select STUFF((SELECT distinct ',',  QUOTENAME(FieldName), ' = ' , CASE WHEN XMLPath IS NULL THEN 'NULL' ELSE '(SELECT TOP 1 c.value(''(' + (REVERSE(LEFT(REVERSE(XMLPath), CHARINDEX('/', REVERSE(XMLPath)) - 1)))  + ')[1]'',''VARCHAR(50)'') FROM  @xml.nodes(''' + REPLACE(XMLPath,'/' + REVERSE(LEFT(REVERSE(XMLPath), CHARINDEX('/', REVERSE(XMLPath)) - 1)), '') + ''') t(c)) ' END 
from IntegracaoOpcaoLayout
where FlagIntegration = 1 
AND XMLPath IS NOT NULL
AND IntegrationId > @begin
AND IntegrationId <= @groupsize
order by QUOTENAME(FieldName) asc

FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')  ,1,1,''))

set @QUERY2 = N'
UPDATE IntegrationData SET ' + @cols + ' WHERE XmlReceiverID = @XMLReceiverID
';
EXEC (@QUERY2)
--PRINT @QUERY2
END 
set @QUERY4 = N'    
UPDATE XMLReceiver SET FlagIntegration = 1 WHERE XMLReceiverID = @XMLReceiverID AND FlagImportData = 0
FETCH NEXT FROM XMLRowItem into @XMLReceiverID,@xml
END
PRINT @XMLReceiverID
CLOSE XMLRowItem

DEALLOCATE XMLRowItem
COMMIT TRANSACTION
';
EXEC (@QUERY4)
--PRINT @QUERY4

If I run this code printing the query strings and execute it all it works fine!

But if I run normally, I get error bellow (about nor declaring the variable declared at first exec statement):

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '@XMLReceiverID'.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@xml".

Has anyone a work around or a suggestion?

Here is the query printed:

BEGIN TRANSACTION
DECLARE @xml XML 
DECLARE @XMLReceiverID int
DECLARE XMLRowItem CURSOR FOR
SELECT XMLReceiverID,XMLContent FROM XMLReceiver WHERE FlagImportData = 0 and Typology = 'XXXX'
OPEN XMLRowItem
FETCH XMLRowItem INTO @XMLReceiverID,@xml
WHILE @@Fetch_Status = 0

BEGIN

INSERT INTO IntegrationData (XmlReceiverID) 

SELECT @XMLReceiverID

UPDATE IntegrationData SET [Field1] = (SELECT TOP 1 c.value('(test1)[1]','VARCHAR(50)') FROM  @xml.nodes('/xmlPath/test1') t(c)) ,
[Field2] = (SELECT TOP 1 c.value('(test2)[1]','VARCHAR(50)') FROM  @xml.nodes('/xmlPath/test2') t(c)) ,
[Field3] = (SELECT TOP 1 c.value('(test3)[1]','VARCHAR(50)') FROM  @xml.nodes('/xmlPath/test3') t(c)) 
UPDATE XMLReceiver SET FlagImportData = 1 WHERE XMLReceiverID = @XMLReceiverID AND FlagImportData = 0
FETCH NEXT FROM XMLRowItem into @XMLReceiverID,@xml
END
PRINT @XMLReceiverID
CLOSE XMLRowItem

DEALLOCATE XMLRowItem
COMMIT

Please, post table DDL, sample data, and expected results, otherwise we can only guess what you actually need. If you really feel your problem can only be solved by using dynamc T-SQL, please, read the following article on the subject, written by Erland Sommarskog: sommarskog.se/dynamic_sql.html ML — Matija Lah
– Matija Lah, Commented Jun 26, 2013 at 13:55

Adam Haines · Accepted Answer · 2013-06-26 13:54:47Z

1

The reason your code is throwing errors is because all of your code is out of scope.

This is your problem.

 set @QUERY2 = N'
    UPDATE IntegrationData SET ' + @cols + ' WHERE XmlReceiverID = @XMLReceiverID
    ';
    EXEC (@QUERY2)
    --PRINT @QUERY2
    END 
    set @QUERY4 = N'    
    UPDATE XMLReceiver SET FlagIntegration = 1 WHERE XMLReceiverID = @XMLReceiverID AND FlagImportData = 0
    FETCH NEXT FROM XMLRowItem into @XMLReceiverID,@xml
    END
    PRINT @XMLReceiverID
    CLOSE XMLRowItem

    DEALLOCATE XMLRowItem
    COMMIT TRANSACTION
    ';

You can see that you reassign @query2 with a statement that requires @XMLReceiverID, but the variable has not been declared for the new @query2 assignment. The same thing holds for @query 4. The two variables @XML,@XMLReceiverId are not in the same scope as @Query1.

Essentially, you need to concatenate all statements into a single variable or combine them together into a single execute.

answered Jun 26, 2013 at 13:54

Adam Haines

9205 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

raddesso Over a year ago

The problem is I can't concatenate then all because of the max size of a variable. The query becomes longer than 8000. I will need a while to execute multiple updates because the full update is longer than 8000

bastos.sergio Over a year ago

can't you change the variable to varchar(max)?

Adam Haines Over a year ago

What version of SQL Server are you using? Can you use VARCHAR(MAX) (SQL 2005+) instead?

raddesso Over a year ago

the varchar(max) has 8000 chars as maxsize

Adam Haines Over a year ago

"The varchar(max) has 8000 chars as maxsize" -- This is not true.. It holds up to 2 GB of data.

|

Collectives™ on Stack Overflow

Executing multiple query strings using a variable declared at first one

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related