How to create dynamic SQL queries inside CURSOR

Question

I have to dynamically create a query inside cursor

DECLARE @id VARCHAR(10)

declare @loc varchar(25)
set @loc = '/MainItem/SubItem';

declare @query varchar(max)     

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT * FROM @tempcolumnname

OPEN myCursor
FETCH NEXT FROM myCursor INTO @id

WHILE @@FETCH_STATUS = 0 
BEGIN
    set @query = 'SELECT * FROM OPENXML(@hdoc, '+@loc+', 3) WITH (code_db_key int)'
    exec (@query)    

    FETCH NEXT FROM myCursor INTO @id
END

but executing this throws an exception

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@hdoc"

Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement
must be terminated with a semicolon.`

But when I am executing the same query outside the cursor, it is working fine.

hdoc is declared at the start of the procedure. Its not shown here but is present — Veer
– Veer, Commented May 6, 2013 at 8:42
Asking just to confirm : Does "Dynamic SQL" work outside cursor and not just the normal SQL statement ? — Ravi Singh
– Ravi Singh, Commented May 6, 2013 at 8:49
Have you added PRINT @querry just before the EXEC to make sure that the dynamic SQL is really what you expect? — Pondlife
– Pondlife, Commented May 6, 2013 at 14:26

marc_s · Accepted Answer · 2013-06-27 20:33:16Z

1

In cursor you have to again execute your xml file , with xml output declaration.

DECLARE @id VARCHAR(25)

declare @loc varchar(25)
set @loc = '/MainItem/SubItem';

declare @query varchar(max) 

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
     SELECT * FROM @tempcolumnname

OPEN myCursor
FETCH NEXT FROM myCursor INTO @id

WHILE @@FETCH_STATUS = 0 
BEGIN
    set @query = 'DECLARE @hdoc INT;           
    EXEC sp_xml_preparedocument @hdoc OUTPUT,'''+ @info+'''
        Select Statement
        Insert Statement exec (@query)      

    FETCH NEXT FROM myCursor INTO @id       
END

CLOSE myCursor
DEALLOCATE myCursor

edited Jun 27, 2013 at 20:33

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 7, 2013 at 4:34

Veer

1,6033 gold badges19 silver badges42 bronze badges

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

Comments

marc_s · Accepted Answer · 2014-02-03 17:13:35Z

0

Try this :

DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
set @query = 
'SELECT * FROM OPENXML(@Temphdoc, '''+@loc+''', 3) WITH (code_db_key int)';
SET @ParmDefinition = N'@Temphdoc varchar(1000)';

/* This can be in cursor loop */

EXECUTE sp_executesql @query, @ParmDefinition,
                      @Temphdoc = @hdoc;

edited Feb 3, 2014 at 17:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 6, 2013 at 8:58

Ravi Singh

2,08013 silver badges29 bronze badges

Comments

marc_s · Accepted Answer · 2014-02-03 17:13:52Z

0

Change

set @query = 'SELECT * FROM OPENXML(@hdoc, '+@loc+', 3) WITH (code_db_key int)'

to

set @query = 'SELECT * FROM OPENXML(@hdoc, '+@loc+', 3) WITH (code_db_key int);'

--

Ok, try this,

set @query = CONCAT('SELECT * FROM OPENXML(@hdoc,',+@loc+,', 3) WITH (code_db_key int);')

edited Feb 3, 2014 at 17:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 6, 2013 at 8:22

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

6 Comments

Mad Dog Tannen Over a year ago

Im trying different things atm.

Mad Dog Tannen Over a year ago

And like Ravi says, there is no declare for @hdoc

Mad Dog Tannen Over a year ago

There is also no declare for @tempcolumnname

Veer Over a year ago

now its throwing other exception Msg 102, Level 15, State 3, Line 171 Incorrect syntax near ')'.

Veer Over a year ago

all declare statement are there at the start of procedure . Its a long procedure so I have not copied everything.

|

Collectives™ on Stack Overflow

How to create dynamic SQL queries inside CURSOR

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related