6

I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional way like the below code.

CREATE PROC test @var1 CHAR(1)
as
BEGIN
IF(@var1 = X)
BEGIN 
SELECT * INTO #result
FROM TABLE1
END
IF(@var1 = Y)
BEGIN 
SELECT * INTO #result
FROM TABLE2
END
IF(@var1 = Z)
BEGIN 
SELECT * INTO #result
FROM TABLE3
END

SELECT * FROM #result r
END

The objective is to finally have a table named #result with columns based on the variable(@var1) value

Edit 1: Since this is a good candidate for the use of dynamic SQL, as shown below, but I won't be able to use the #result table outside dynamics SQL scope which is what I need.

CREATE PROC test @var1 CHAR(1)
as
BEGIN
-- USING  dynamic sql
DECLARE @sql VARCHAR(MAX)
IF(@var1 = 'X')
BEGIN 
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id] INTO #result
FROM sys.tables t'
END
IF(@var1 = 'Y')
BEGIN 
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id] INTO #result
FROM sys.tables t'
END
IF(@var1 = 'Z')
BEGIN 
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id],t.[parent_object_id] INTO #result
FROM sys.tables t'
END

EXEC (@sql)
SELECT * FROM #result r
END
5

Supported?

I know this is tagged as 2008R2. Since that's officially out of support, perhaps an upgrade is in your future. If you end up on a version of SQL Server > 2012, you can use code like this:

CREATE OR ALTER PROCEDURE dbo.dynamic_temp ( @TableName NVARCHAR(128))
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #t ( Id INT );
    DECLARE @sql NVARCHAR(MAX) = N'';

    IF @TableName = N'Users'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Users AS u WHERE u.Reputation > @i';
        END;

    IF @TableName = N'Posts'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Posts AS p WHERE p.Score > @i';
        END;

    SELECT   column_ordinal, name, system_type_name
    INTO     #dfr
    FROM     sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
    ORDER BY column_ordinal;

    DECLARE @alter NVARCHAR(MAX) = N'ALTER TABLE #t ADD ';

    SET @alter += STUFF((   SELECT   NCHAR(10) + d.name + N' ' + d.system_type_name + N','
                            FROM     #dfr AS d
                            WHERE    d.name <> N'Id'
                            ORDER BY d.column_ordinal
                            FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(4000)'), 1, 1, N'');

    SET @alter = LEFT(@alter, LEN(@alter) - 1);

    EXEC ( @alter );

    INSERT #t
    EXEC sys.sp_executesql @sql, N'@i INT', @i = 10000;

    SELECT *
    FROM   #t;

END;
GO

The idea is to use dm_exec_describe_first_result_set to determine which columns your result produces, along with their datatypes. We can use that to generate a dynamic ALTER TABLE statement to add those columns to a base #temp table created outside the scope of the dynamic SQL.

That makes inserting and selecting data from it pretty easy.

I was blogging about this when I remembered this question. Again, sorry there's nothing as easy for < 2012.

2

You are thinking about the problem the wrong way.

You can use dynamic SQL to do this - by defining your result shape within the statement, but creating the temporary table outside of it. Like so:

CREATE PROC test @var1 CHAR(1)
as
BEGIN
-- USING  dynamic sql
DECLARE @sql VARCHAR(MAX)
IF(@var1 = 'X')
BEGIN 
SET @sql ='SELECT t.[name]
                 ,t.[object_id]
                 ,t.[principal_id]
             FROM sys.tables t'
END
IF(@var1 = 'Y')
BEGIN 
SET @sql ='SELECT t.[name]
                 ,t.[object_id]
                 ,t.[principal_id]
                 ,t.[schema_id]
             FROM sys.tables t'
END
IF(@var1 = 'Z')
BEGIN 
SET @sql ='SELECT t.[name]
                 ,t.[object_id]
                 ,t.[principal_id]
                 ,t.[schema_id]
                 ,t.[parent_object_id]
             FROM sys.tables t'
END

INSERT INTO #result
EXEC (@sql);

SELECT * FROM #result r
END

This works because you can use the results generated by the EXECUTE statement as an input to the INSERT statement, this then allows you to build your temporary table outside of the dynamic SQL. Then you can access it as required.

There are limitations, such as the dynamic SQL itself cannot contain an INSERT EXEC statement as they cannot be nested. For more information see INSERT (Transact-SQL), specifically the section on execute_statement.

EDIT

Based on feedback and testing the above it seems that this no longer works (though I do remember doing this a while back - maybe I am just getting old!). The only other approach I can think of to get this to work is to use OPENROWSET. Generally I wouldn't recommend it, but it seems it is the only way to meet your requirements.

You will need to ensure that you server is configured correctly beforehand:

EXEC sp_configure 'Show Advanced Options', 1  
GO
RECONFIGURE 
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

Then you can use the below to create the temp table:

SELECT * 
  INTO #results 
  FROM OPENROWSET(
         'SQLNCLI', 
         'Server=(local);Trusted_Connection=yes;', -- replace with actual server/instance
         'SELECT * FROM sys.tables'); -- replace with query

SELECT * FROM #results;

This is extremely hacky and I wouldn't do it personally, I would always prefer to push as much as I could down into the dynamic SQL statement itself. But if you really can't do it any other way, then the above will work for you.

FURTHER EDIT

The above will not work when placed within the body of the IF statements because the second statement will tell you that the table has already been defined.

Based on all the information you have given, unless you are able to use Global Temp tables, push all of the logic into the dynamic SQL or create intermediate tables such as with the CREATE TABLE statement. Then what you want to do cannot be done.

0

@biju, what about to put whole code in dynamic sql and put the select as well . Then there will not be any issue of of table scope.

  • well that's an alternative but I need the table #result outside the dynamic SQL , so I won't be able to use dynamic SQL. – Biju jose Mar 10 '19 at 16:16
0

Use a global temporary table instead of a session-scoped one (##table instead of #table). This will allow you to create the table conditionally using dynamic SQL and then access it in the original session.

DECLARE @var1 CHAR(1) = 'X'

IF(@var1 = 'X')
BEGIN 
    EXEC ('SELECT t.[name],t.[object_id],t.[principal_id] INTO ##results
    FROM sys.tables t')
END
ELSE IF(@var1 = 'Y')
BEGIN 
    EXEC ('SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id] INTO ##results
    FROM sys.tables t')
END
IF(@var1 = 'Z')
BEGIN 
    EXEC ('SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id],t.[parent_object_id] INTO ##results
    FROM sys.tables t')
END

SELECT * FROM ##results

DROP TABLE ##results

More info on Temporary Tables.

  • 1
    I was also reaching the same point you said, but one downside of this using global variables is if the proc run concurrently there will be collision causing the object already exists, adding a unique id again puts me back to use dynamic sql to create #results – Biju jose Mar 11 '19 at 8:57
0

Option 1

It looks like your return types for each of your results are closely related enough that you could stuff everything into the same table with a couple of CASE statements, if you're cool with that.

CREATE TABLE #results
(
    [name] SYSNAME,
    [object_id] INT,
    [principal_id] INT,
    [schema_id] INT,
    [parent_object_id] INT
)

INSERT INTO #results
SELECT t.[name], 
    t.[object_id], 
    t.[principal_id], 
    CASE 
        WHEN @var1 = 'X' THEN null 
        WHEN @var1 = 'Y' OR @var1 = 'Z' THEN t.[schema_id] 
    END AS [schema_id], 
    CASE 
        WHEN @var1 = 'X' OR @var1 = 'Y' THEN null 
        WHEN @var1 = 'Z' THEN t.[parent_object_id] 
    END AS [parent_object_id]
FROM sys.tables AS t

SELECT r.[name], r.[object_id], r.[principal_id], r.[schema_id], r.[parent_object_id]
FROM #results as r

DROP TABLE #results

If you're ok with always returning the same result set with two nullable columns on the end then you're good at this point otherwise if you need to return the correlating return type depending on your variable @var1, then you can just implement your logic checks for your SELECT statement at the end like so:

IF (@var1 = 'X')
BEGIN
    SELECT r.[name], r.[object_id], r.[principal_id]
    FROM #results as r
END

IF (@var1 = 'Y')
BEGIN
    SELECT r.[name], r.[object_id], r.[principal_id], r.[schema_id]
    FROM #results as r
END

IF (@var1 = 'Z')
BEGIN
    SELECT r.[name], r.[object_id], r.[principal_id], r.[schema_id], r.[parent_object_id] 
    FROM #results as r
END

Option 2

Alternatively if you prefer continuing on via a Dynamic SQL solution, you can fix the concurrency issue with a global temp table by generating a unique name for your global temp table by doing something like the following:

DECLARE @UniqueTableId AS VARCHAR(50) = (SELECT CAST(NEWID() AS VARCHAR(50)))

EXEC ('SELECT t.[name],t.[object_id],t.[principal_id] INTO ##results_' + @UniqueTableId + '
    FROM sys.tables t')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.