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.

CodePudding user response：

While you do need to use a literal integer (or none at all) with IDENTITY in CREATE TABLE statements, you can reset the IDENTITY seed value after you've created the table using DBCC CHECKIDENT which does support parameters:

Also:

• When working with SQL Server you should always fully-qualify object names with their parent schema (the default is dbo), otherwise objects might be created in a different schema and names won't be resolved when addressed from objects in other schemas (it helps performance too).
• Also, here's some unsolicted database design and data-modelling pointers:
  • Generally speaking, all columns should be NOT NULL unless you're absolutely certain otherwise.
    • Especially for textual columns: there's nothing quite as annoying as having to deal with a textual column containing both NULL and blank '' values and no clue about if there's any semantic difference between the two.
      • Bonus bingo: if the column also contains non-empty but whitespace strings. This is what CHECK constraints are for, folks!
  • Also, annoyingly, if you don't specify either NOT NULL or NULL in a CREATE TABLE column specification then SQL Server defaults to assuming NULL.
• Names, or really any textual column containing human-readable text should generally be nvarchar and not varchar.
  • This doesn't apply to other RDBMS where their varchar types natively support Unicode.
  • The exception to this is when using SQL Server's oddly-implemented support for UTF-8, but that's another discussion.

Anyway, like this:

CREATE TABLE dbo.Pets (
    PetId   int           NOT NULL IDENTITY PRIMARY KEY,
    PetName nvarchar(255) NOT NULL
);

GO

DBCC CHECKIDENT ( dbo.Pets, RESEED, @newIdentitySeed );

• The GO statement is for the benefit of SSMS; if you're using this from program code you might need to move the DBCC CHECKIDENT command to a separate command-batch.

CodePudding user response：

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;