declaring temp table in both branches of a sql server stored proc conditional

Question

I want to have a construct in a stored procedure that defines a temporary table one way in one branch of an if-else and another way in the other branch, but this won't compile b/c it says the table is already created. I am imagining something like the following:

if
begin
  IF OBJECT_ID(N'tempdb..#tbl') IS NOT NULL DROP TABLE #tbl
  create table #tbl (A int, B int)
end
else 
begin
  IF OBJECT_ID(N'tempdb..#tbl') IS NOT NULL DROP TABLE #tbl
  create table #tbl (A int, B int, C int)
end

which seems like it should be well defined.

Is there a better way to do this than declaring it empty and then repeatedly altering it to add the columns I want in the branches? (this is just kind of ugly)

Answer 1

can't you put your drop table outside both if clauses?

Answer 2

Yes, apparently you can't do that. I've learned something new today.

It's a bit difficult to propose a solution without knowing more about what you need to do, where your primary key for the temp table would be, or what your conditional logic is based on.

If you are just adding additional non-key columns, you can use a single temp table with the additional columns allowing nulls.

Later, you can use the additional columns or not, based upon the same logic you used to populate the temp table.

If you are changing the primary key of the table, you'll need to use two separate tables.

Depending on the logic in your procedure, you could possibly use the SELECT INTO syntax to create the temp table based on a query result.

I haven't tried it, but you could probably use a dynamic SQL statement to create the table. I can't recommend doing this though, unless there is no other solution.

Answer 3

Example dynamic sql pivot:

CREATE PROCEDURE dbo.MyProc
    @StartMonth         INT = 1
    ,@StartYear         INT
    ,@EndMonth          INT = 12
    ,@EndYear           INT
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @SQLString VARCHAR(MAX)
    DECLARE @CalendarMonths VARCHAR(MAX)
    SET @CalendarMonths = ''

    IF @StartYear IS NULL
        SET @StartYear = DATEPART(yyyy, getdate())

    IF @EndYear IS NULL
        SET @EndYear = DATEPART(yyyy, getdate())

    DECLARE @YearCounter INT
    DECLARE @MonthCounter INT

    SET @YearCounter = @StartYear
    SET @MonthCounter = @StartMonth

    WHILE @YearCounter <= @EndYear
    BEGIN
        IF @YearCounter = @StartYear
            SET @MonthCounter = @StartMonth
        ELSE
            SET @MonthCounter = 1

        WHILE (@YearCounter = @EndYear AND @MonthCounter <= @EndMonth) OR (@YearCounter <> @EndYear AND @MonthCounter <= 12)
        BEGIN
            SET @CalendarMonths = @CalendarMonths + '[' + CAST(@MonthCounter AS VARCHAR) + '/' + CAST(@YearCounter AS VARCHAR) + '],'
            SET @MonthCounter = @MonthCounter + 1
        END

        SET @YearCounter = @YearCounter + 1
    END

    SET @CalendarMonths = @CalendarMonths + ',@'    -- add token to mark last ',' location
    SET @CalendarMonths = REPLACE(@CalendarMonths, ',,@', '')   -- remove last ','

    SET NOCOUNT OFF


    SET @SQLString = 
'
SELECT
    *
FROM
(
    SELECT Units, CAST(CalendarMonth AS VARCHAR) + ''/'' + CAST(CalendarYear AS VARCHAR) AS Calendar, GroupID
    FROM dbo.MyTable
) ptg
PIVOT
(
    SUM(Units) 
    FOR Calendar IN
    (
'
        + CHAR(9)
        + CHAR(9)
        + @CalendarMonths +
'
    )
) pvt
'

    --PRINT(@SQLString)
    EXEC(@SQLString)

END
GO