SQL INDENTITY DOESN'T RECEIVE PARAMETERS

Question

I'm trying to set IDENTITY seed parameter while creating table, getting it from a var. Something like this

DECLARE @MaxID INTEGER
SET @MaxID = (SELECT TOP 1 ID FROM dbo.ProductQuotes ORDER BY ID DESC) + 1;

CREATE TABLE [dbo].[Z](
 [ID] int PRIMARY KEY not null IDENTITY(@MaxID,1),
 [Number] int NULL,
 [Name] nvarchar(50) COLLATE Cyrillic_General_CI_AS NULL
) ON [PRIMARY]

GO

Error is "incorrect syntax near '@MaxID' (in this row [ID] int PRIMARY KEY not null IDENTITY(@MaxID,1) )

But I'm not sure it's about syntax at all. Can somebody explain me where am I wrong here? :)

Answer 1

This cannot be parameterised.

You would need to use dynamic SQL as below.

DECLARE @MaxID INTEGER

SELECT @MaxID = 1 + ISNULL(MAX(ID),0) FROM dbo.ProductQuotes

DECLARE @Script NVARCHAR(MAX) = 
N'
CREATE TABLE [dbo].[Z](
 [ID] int PRIMARY KEY not null IDENTITY(' + CAST(@MaxID AS NVARCHAR(10)) + ',1),
 [Number] int NULL,
 [Name] nvarchar(50) COLLATE Cyrillic_General_CI_AS NULL
) ON [PRIMARY]
'

EXEC (@Script);

I assume you will take precautions to ensure that ProductQuotes cannot be subject to any inserts during the import process.

Answer 2

Something like

DECLARE @MaxID INTEGER
DECLARE @SQL varChar(4000)
SET @MaxID = (SELECT TOP 1 ID FROM dbo.ProductQuotes ORDER BY ID DESC) + 1;

Set @SQL = 'CREATE TABLE [dbo].[Z]([ID] int PRIMARY KEY not null IDENTITY(' +
Convert(VarChar(8),@MaxID) + 
',1), [Number] int NULL, [Name] nvarchar(50) COLLATE Cyrillic_General_CI_AS NULL) ON [PRIMARY]
Exec(@sql)

Build the sql statement using the teh value of @MaxID then execute it.