Dynamic Query output into Cursor
-
Hi,
I have a dynamic query like this:
Declare @Qry As VarChar(1000)
Set @Qry='Select ' + @col1 + ',' + @col2 + ' from employee '
I want to store the output the query in a cursor or in a variable of table datatype.
For static queries we can do like
Set @cursor=Cursor
Forward only static for
select * from employee
but for dynamic queries how can do?
Any ideas?
Regards
Daniel
-
In 99% of cases, cursor is not necessary. If you need to store the result, better do it in a table (permanent or temporary) or table variable. Make the INSERT part of the dynamic SQL (if you really need to use dynamic)... like:
Set @Qry='INSERT INTO tbl_name(col1, col2) Select ' + @col1 + ',' + @col2 + ' from employee '
Table variable is not an option here, since it would have to be declared inside the dynamic SQL in order for insert to succeed, but then again would not exist once the execution of dynamic SQL is finished.
-
Agree with Vladan
However the answer to your question is
Set @Qry='DECLARE mycurs CURSOR FOR SELECT ' + @col1 + ',' + @col2 + ' FROM employee '
EXEC(@Qry)
OPEN mycurs
FETCH NEXT FROM mycurs
... etc
CLOSE mycurs
DEALLOCATE mycurs
Far away is close at hand in the images of elsewhere.
Anon. -
Use a temp table...
Create #tmpTable(definition here)
Insert #tmpTable
Exec(@Qry)
If the cursor is really needed,
...cursor for select * from #tmpTable
David's answer will no work as Vladan mentioned, since the cursor is declared inside the dynamic SQL, it would be out of scope once the execution of dynamic SQL is finished.
-
David's answer works because he's using an old-fashioned cursor not a cursor variable. The old-fashioned cursors have a scope of the entire session; they don't go away until you deallocate or kill the session.
Try it yourself:
exec('declare cur cursor for select a=1')
declare @a int
open cur
fetch next from cur into @a
select @a
close cur
deallocate cur
-
My apologies David.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply