Dynamic SQL, sp_executesql, and rebuilding the dynamic sql statement - Part 2

Question

Part 2: In his article, "Dynamic Search Conditions in T-SQL...for SQL 2005 and Earlier", Erland Sommarskog gives an example of how to use dynamic sql with sp_executesql.

http://www.sommarskog.se/dyn-search-2005.html#sp_executesql

SELECT @sql =                                                  -- 19
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 20
        c.CustomerID, c.CompanyName, c.Address, c.City,        -- 21
        c.Region,  c.PostalCode, c.Country, c.Phone,           -- 22
        p.ProductID, p.ProductName, p.UnitsInStock,            -- 23
        p.UnitsOnOrder                                         -- 24
 FROM   dbo.Orders o                                           -- 25
 JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 26
 JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 27
 JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 28
     WHERE  1 = 1'                                             -- 29
                                                               -- 30
IF @orderid IS NOT NULL                                        -- 31
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +         -- 32
                        ' AND od.OrderID = @xorderid'          -- 33
                                                               -- 34
IF @fromdate IS NOT NULL                                       -- 35
   SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'       -- 36

etc...

In another article on dyanmic sql, he writes:

Temp tables created in the dynamic SQL will not be accessible from the calling procedure since they are dropped when the dynamic SQL exits.

So my question revolves around this issue: if you've already built the dynamic sql statement once, and the SELECT statement in the @sql returns col1, col2, col3, col4, etc., what should you do if you then have to re-query that same result set in different ways? For instance, if you had to return another result set that had a COUNT and grouped by col1, and another result set with a COUNT grouped by col2, or some other criteria whose foundation is the result set from the original @sql?

Would you have to re-build the @sql all over again for each different scenario, so you end up with @sql, @sql_2, @sql_3,...?

In this case, would dynamic sql still be the best option, or would it be better to use static sql that inserts into a #temp table, so you can re-query the #temp table results?

Answer 1

In SQL Server if you create a temp table in dynamic SQL you can't access that temp table from non-dynamic SQL in the calling procedure. As you said.

You have a few options:

• Create the temp table beforehand (in non-dynamic sql). It's then usable from dynamic sql so you can insert into it.
• If you don't know the temp table structure then the previous option seems not so helpful. But you can create the temp table first with a single dummy column, then modify the structure of the temp table from dynamic sql. Seems a bit icky but handy sometimes.
• Use ## global temp tables
• Use real tables, perhaps with a 'batch' identifier to separate your results from those of anyone else also inserting into the real tables.
• Use dynamic SQL to create/alter a procedure which creates the temp table how you want it. You can then call that procedure from non-dynamic SQL. Quite a bit of work, but handy sometimes.
• Rebuild the dynamic sql in different ways, as you suggest in your question, and execute them separately. Not so performant as you're processing the data multiple times, but often that doesn't matter.

Answer 2

Another way to do this, though more limited in use than your own find, would be to use the INSERT ... EXEC device.

INSERT INTO sometable    /* or @vartable or #temptable */
EXEC sp_executesql @sql  /* and @params, if needed */

You cannot use this in an SP that itself is being called in another INSERT...EXEC.

Answer 3

Ok, so here's an example of a solution around this issue from the msdn site.

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/3ed6885a-fe18-41c0-bce4-76c44c737a1b

The issue is this: if you create temp table, and you create your temp table INSIDE your dynamic sql, you have no chance to access the temp table OUTSIDE of the dynamic sql. However, if you first create your temp table OUTSIDE of the dynamic sql, then populate the temp table INSIDE the dynamic sql, you can access the populated temp table OUTSIDE of the dynamic sql.