Trying to add a column to a temp table fails

Question

I have looked through this a number of times this morning and cannot find the issue. Its probably dead simple and I'll feel like an idiot when someone points it out. What would this SQL fail?

-- Get CurrentRowCount
DECLARE @MaxID INT

SELECT @MaxID = ISNULL(MAX(WorkTypeID),0)
FROM caWorkType

ALTER TABLE #WorkType ADD _RowID INT NOT NULL IDENTITY(@MaxID,1)

Error I'm getting is: "Incorrect syntax near '@MaxID'" I have checked and @MaxID is being set to 45 by the select statement. The temp table #WorkType also does not have a column named "_RowID"

Answer 1

The IDENTITY clause doesn't like variables. Use sp_executesql to run the ALTER statement:

-- Get CurrentRowCount
DECLARE @MaxID INT

SELECT @MaxID = ISNULL(MAX(WorkTypeID),0)
FROM caWorkType

DECLARE @sql varchar(max);
SET @sql = 'ALTER TABLE #WorkType ADD _RowID INT NOT NULL IDENTITY(' +
        CAST(@MaxID as varchar) +
        ',1)';
EXEC sp_executesql @statement = @sql;

Answer 2

It's not the act of trying to add a column to a temp table that fails, but you cannot use a variable in specifying the values for IDENTITY:

NOT OK:

ALTER TABLE #WorkType ADD _RowID INT NOT NULL IDENTITY(@MaxID,1)

OK:

ALTER TABLE #WorkType ADD _RowID INT NOT NULL IDENTITY(55,1)

Marc