Creating temporary table dynamically using condition

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

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.

StackExchange Database Administrators Q#231781, answer score: 7