'PUT parameter into IDENTITY(1,1) property in SQL Table Creation script

I would like to set custom Identity with parameters for example

CREATE TABLE Pets (
    PetId int IDENTITY(@Parameter,1) PRIMARY KEY, 
    PetName varchar(255)
    );

My SQL parser does not accept such syntax.

sqlsql-servertsqlssmsscript


Solution 1:[1]

You can use dynamic SQL but this doesn't seem right.

DECLARE @Parameter int = 1000;

DECLARE @sql nvarchar(max) = N'CREATE TABLE dbo.Pets 
  (
    PetId int IDENTITY(' 
      + CONVERT(varchar(12), TRY_CONVERT(int,@Parameter)) 
      + ',1) PRIMARY KEY, 
    PetName varchar(255)
  );';

EXEC sys.sp_executesql @sql;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions