Creating temporary table dynamically using condition
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 &gt; @i';
        END;

    IF @TableName = N'Posts'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Posts AS p WHERE p.Score &gt; @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 &lt;&gt; 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 &lt; 2012.