An attempt to add a column to the temporary table failed

Question

I looked through it several times this morning and cannot find the problem. He is probably dead, and I will feel like an idiot when someone indicates this. What will happen to this SQL?

-- Get CurrentRowCount
DECLARE @MaxID INT

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

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

The error I get is: “The syntax is incorrect next to“ @MaxID. ”I checked and @MaxID is set to 45 using the select statement. There is also no column named“ _RowID ”in temp table #WorkType

Answer 1

This is not an attempt to add a column to a temporary table that fails, but you cannot use a variable when specifying 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)

Mark

Answer 2

IDENTITY . sp_executesql ALTER:

-- 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;