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.
Modification Type: | Major | Last Reviewed: | 10/16/2003 |
---|
Keywords: | kbBug kbCodeSnippet kbpending KB271827 |
---|
|