Dynamic SQL and Temp Tables/Table Variables
-
In case you missed my previous post, I'm supporting (sorta) a DB in an app from a vendor. The vendor provided some reporting queries for reports the customer wants. I'm trying to run one of them using a cursor against the primary table (not a cursor fan but this is a one-time thing) to show me the primary table entries that will return data for the query so I can use their PKs to test the Stored Procedure. I wrapped his code in a cursor loop against the primary table. It runs fine. The problem is there are thousands of rows in the Primary table and only a few of them have data. When I run the query in QA it runs into the max allowed recordsets to be displayed in the output window before hitting a row that actually returns data. I solved this with the other queries by selecting into a temp table, checking for @@rowcount and selecting the data from the temp table if @@rowcount>0. This cut out the Primary table entries that don't return data and gave me a nice list of PKs I can use to test.
The problem with this last query is that the main Select statement (the one that actually produces the final results) is constructed and executed using dynamic SQL. I can't just stick an "INTO #temp" into the dynamic SQL because the temp table is out of scope once I return from the Exec call.
Is there a way to do this without a lot of agita? I was thinking about copying the query, changing the Select to a "Select count(*)..." and then only running the full query if the first one returns > 0, but I don't know how to get the count (or @@rowcount) out of the first query...
Any ideas?
-
Lots of ideas
But since this is just a hit and run thing, why don't you use a global temporary table? Just add another # as in "...INTO ##temp..."
A global temp will stay in scope.
-
Didn't know you could do that.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply