BUG: DOC Error: Cannot Use Variables as SEED Parameter to the IDENTITY() Function (271827)



The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)

This article was previously published under Q271827
BUG #: 58240, 58294 (SQLBUG_70)
BUG #: 235891 (SHILOH)

SYMPTOMS

The IDENTITY function is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. SQL Server Books Online ought to include information stating that variables are not allowed as parameters to this function.

SQL Server 7.0 accepts a variable for the SEED parameter but does not return the expected results. SQL Server 2000 returns the following error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@myident'.

WORKAROUND

To work around this problem, dynamically build the Transact-SQL command into a string, and then EXECute that string:
DECLARE @SQL varchar(8000)
DECLARE @myident int
SET @myident = 100
SET @sql = 'SELECT IDENTITY(INT, ' + CAST(@myident as varchar) + ', 1)    AS ident INTO #T2'
EXEC(@sql)
SELECT @@identity
				

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0 and SQL Server 2000.

MORE INFORMATION

To reproduce this behavior, run the following script:
DECLARE @myident int
SET @myident = 100
SELECT IDENTITY(int, @myident, 1) AS ident INTO #T1
DROP TABLE #T1
SELECT @@identity
				

Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbBug kbCodeSnippet kbpending KB271827