October 14, 2011 at 9:20 am
Hi All,
I am trying to execute 2 dynamic sql statements within a cursor. But i came to know only 1 statement is running.
Scenario:
set @sql = 'stmt 1'
exec (@sql)
print @sql
set @sql = 'stmt 2'
exec (@sql)
print @sql
Above stmt's are within a cursor, it executes stmt2 and stmt1 doesn't executes. print stmt displays both queries.
when i comment execution of stmt2 then i can see the result of stmt1.
Please do the needful.
Thanks in Advance.
Regards,
Naveen
October 14, 2011 at 10:12 am
You probably need to use separate variables instead of reusing the same one.
The bigger question, can you get rid of the cursor? Can you get rid of the dynamic sql? Dynamic inside a cursor is a performance nightmare. Could this whole process be done set based? If you want some help getting rid of your cursor there are lots of people on here (including myself) willing and able to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 10:23 am
Hi Sean,
Thanks for the quick reply.
I tried using different variables, even though it's same.
It would be great help if you can let me know to get rid of cursor.
Regards,
Naveen. N
October 14, 2011 at 10:27 am
I would be happy to help but I need a bit more details. ddl, sample data (inserts), desired output based on your sample data. And of course the dml of your current code. If you need some details or instructions on how to find and post all this take a look at the first link in my signature. The biggest advantage of posting the details is you will get back tested, accurate and fast code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 10:28 am
You might find this article[/url] a good starting point if you want to try to tackle it on your own.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply