T-SQL Dynamic SQL and Temp Tables

Question

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement in a subsequence dynamic SQL but it seems that a stored procedure does not return a query result to a calling client unless the SQL is fixed.

A simple 2 table scenario: I have 2 tables. Let's call them Orders and Items. Order has a Primary key of OrderId and Items has a Primary Key of ItemId. Items.OrderId is the foreign key to identify the parent Order. An Order can have 1 to n Items.

I want to be able to provide a very flexible "query builder" type interface to the user to allow the user to select what Items he want to see. The filter criteria can be based on fields from the Items table and/or from the parent Order table. If an Item meets the filter condition including and condition on the parent Order if one exists, the Item should be return in the query as well as the parent Order.

Usually, I suppose, most people would construct a join between the Item table and the parent Order tables. I would like to perform 2 separate queries instead. One to return all of the qualifying Items and the other to return all of the distinct parent Orders. The reason is two fold and you may or may not agree.

The first reason is that I need to query all of the columns in the parent Order table and if I did a single query to join the Orders table to the Items table, I would be repoeating the Order information multiple times. Since there are typically a large number of items per Order, I'd like to avoid this because it would result in much more data being transfered to a fat client. Instead, as mentioned, I would like to return the two tables individually in a dataset and use the two tables within to populate a custom Order and child Items client objects. (I don't know enough about LINQ or Entity Framework yet. I build my objects by hand). The second reason I would like to return two tables instead of one is because I already have another procedure that returns all of the Items for a given OrderId along with the parent Order and I would like to use the same 2-table approach so that I could reuse the client code to populate my custom Order and Client objects from the 2 datatables returned.

What I was hoping to do was this:

Construct a dynamic SQL string on the Client which joins the orders table to the Items table and filters appropriate on each table as specified by the custom filter created on the Winform fat-client app. The SQL build on the client would have looked something like this:

TempSQL = "

    INSERT INTO #ItemsToQuery
       OrderId, ItemsId
    FROM
       Orders, Items 
    WHERE
       Orders.OrderID = Items.OrderId AND
       /* Some unpredictable Order filters go here */
      AND
       /* Some unpredictable Items filters go here */
    "

Then, I would call a stored procedure,

CREATE PROCEDURE GetItemsAndOrders(@tempSql as text)
   Execute (@tempSQL) --to create the #ItemsToQuery table

SELECT * FROM Items WHERE Items.ItemId IN (SELECT ItemId FROM #ItemsToQuery)

SELECT * FROM Orders WHERE Orders.OrderId IN (SELECT DISTINCT OrderId FROM #ItemsToQuery)

The problem with this approach is that #ItemsToQuery table, since it was created by dynamic SQL, is inaccessible from the following 2 static SQLs and if I change the static SQLs to dynamic, no results are passed back to the fat client.

3 around come to mind but I'm look for a better one:

1) The first SQL could be performed by executing the dynamically constructed SQL from the client. The results could then be passed as a table to a modified version of the above stored procedure. I am familiar with passing table data as XML. If I did this, the stored proc could then insert the data into a temporary table using a static SQL that, because it was created by dynamic SQL, could then be queried without issue. (I could also investigate into passing the new Table type param instead of XML.) However, I would like to avoid passing up potentially large lists to a stored procedure.

2) I could perform all the queries from the client.

The first would be something like this:

SELECT Items.* FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (dynamic filter)
SELECT Orders.* FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (dynamic filter)

This still provides me with the ability to reuse my client sided object-population code because the Orders and Items continue to be returned in two different tables.

I have a feeling to, that I might have some options using a Table data type within my stored proc, but that is also new to me and I would appreciate a little bit of spoon feeding on that one.

If you even scanned this far in what I wrote, I am surprised, but if so, I woul dappreciate any of your thoughts on how to accomplish this best.

Solution 1

You first need to create your table first then it will be available in the dynamic SQL.

This works:

CREATE TABLE #temp3 (id INT)
EXEC ('insert #temp3 values(1)')

SELECT *
FROM #temp3

This will not work:

EXEC (
        'create table #temp2 (id int)
         insert #temp2 values(1)'
        )

SELECT *
FROM #temp2

In other words:

1. Create temp table
2. Execute proc
3. Select from temp table

Here is complete example:

CREATE PROC prTest2 @var VARCHAR(100)
AS
EXEC (@var)
GO

CREATE TABLE #temp (id INT)

EXEC prTest2 'insert #temp values(1)'

SELECT *
FROM #temp

Solution 2

1st Method - Enclose multiple statements in the same Dynamic SQL Call:

DECLARE @DynamicQuery NVARCHAR(MAX)

SET @DynamicQuery = 'Select * into #temp from (select * from tablename) alias 
select * from #temp
drop table #temp'

EXEC sp_executesql @DynamicQuery

2nd Method - Use Global Temp Table:
(Careful, you need to take extra care of global variable.)

IF OBJECT_ID('tempdb..##temp2') IS NULL
BEGIN
    EXEC (
            'create table ##temp2 (id int)
             insert ##temp2 values(1)'
            )

    SELECT *
    FROM ##temp2
END

Don't forget to delete ##temp2 object manually once your done with it:

IF (OBJECT_ID('tempdb..##temp2') IS NOT NULL)
BEGIN
     DROP Table ##temp2
END

Note: Don't use this method 2 if you don't know the full structure on database.

Solution 3

I had the same issue that @Muflix mentioned. When you don't know the columns being returned, or they are being generated dynamically, what I've done is create a global table with a unique id, then delete it when I'm done with it, this looks something like what's shown below:

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @DynamicTable VARCHAR(255) = 'DynamicTempTable_' + CONVERT(VARCHAR(36), NEWID())
DECLARE @DynamicColumns NVARCHAR(MAX)

--Get "@DynamicColumns", example: SET @DynamicColumns = '[Column1], [Column2]'

SET @DynamicSQL = 'SELECT ' + @DynamicColumns + ' INTO [##' + @DynamicTable + ']' + 
     ' FROM [dbo].[TableXYZ]'

EXEC sp_executesql @DynamicSQL

SET @DynamicSQL = 'IF OBJECT_ID(''tempdb..##' + @DynamicTable + ''' , ''U'') IS NOT NULL ' + 
    ' BEGIN DROP TABLE [##' + @DynamicTable + '] END'

EXEC sp_executesql @DynamicSQL

Certainly not the best solution, but this seems to work for me.

Solution 4

I would strongly suggest you have a read through http://www.sommarskog.se/arrays-in-sql-2005.html

Personally I like the approach of passing a comma delimited text list, then parsing it with text to table function and joining to it. The temp table approach can work if you create it first in the connection. But it feel a bit messier.

Solution 5

Result sets from dynamic SQL are returned to the client. I have done this quite a lot.

You're right about issues with sharing data through temp tables and variables and things like that between the SQL and the dynamic SQL it generates.

I think in trying to get your temp table working, you have probably got some things confused, because you can definitely get data from a SP which executes dynamic SQL:

USE SandBox
GO

CREATE PROCEDURE usp_DynTest(@table_type AS VARCHAR(255))
AS 
BEGIN
    DECLARE @sql AS VARCHAR(MAX) = 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''' + @table_type + ''''
    EXEC (@sql)
END
GO

EXEC usp_DynTest 'BASE TABLE'
GO

EXEC usp_DynTest 'VIEW'
GO

DROP PROCEDURE usp_DynTest
GO

Also:

USE SandBox
GO

CREATE PROCEDURE usp_DynTest(@table_type AS VARCHAR(255))
AS 
BEGIN
    DECLARE @sql AS VARCHAR(MAX) = 'SELECT * INTO #temp FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''' + @table_type + '''; SELECT * FROM #temp;'
    EXEC (@sql)
END
GO

EXEC usp_DynTest 'BASE TABLE'
GO

EXEC usp_DynTest 'VIEW'
GO

DROP PROCEDURE usp_DynTest
GO

View more solutions

Comments

[irag10]

You can definitely return results from dynamic sql back to the calling application. Most answers here address the other points you make.

[SQLMenace]

if you create the temp table in the proc it won't work, you need to create the temp table first, then you can populate it in the proc..see also my example

[Zachary Scott]

I think this works too: insert into #temptable exec ('select ?? from ??');

[Chad]

I would sooner pass XML than CSV . Though more verbose,it allows for the flexibility to modify and pass additional columns. And SQL already knows how to parse XML. But I seen example for passing a client dataset into a server side table variable. Very clean. Even that though, is less desirable than the temp table IMHO that is an approach that is less likely to scale.

[Cade Roux]

@SQLMenace - I see what you are saying. My point was that you CAN return sets from dynamic SQL (and they can use their own temp tables and return from them). I'll add a second example.

[Muflix]

problem is when we dont know the columns definition, how about then ?

[user2864740]

SQL Server 2008+ (or 2005?) supports table-values that can be supplied via the client once defined (works well from, ADO.NET data-sets, eg). SQL Server 2016+ supports JSON.

[natur3]

Assuming you know the table exists take a top 1 from the target table into #someTable. Then truncate #someTable. This is quick and dirty. Disclaimer: The column size of varchars for example may not represent the largest size somewhere in the entire set. If your worried about that build the temp table from sys.tables and sys.columns. You should only need to do this once. It can also be done dynamically if your good with Dynamic SQL

[user3335999]

This will not work. You cannot operate on a table's content before you've executed the dynamic query.