Search code examples
sqlidentitytemp-tables

Using a Parameter As An IDENTITY starting number

I am writing a query which creates some test accounts. I am giving my stored procedure an INT which is a starting number - and want it to simply create accounts which have a number 1 more than the one before it since each account number must be different. I have removed almost all the items from the table, but here is what I am trying to use:

CREATE TABLE #TempAccount (AccountNumber INT IDENTITY((@StartingAccountNumber), 1)

And it is giving me a "incorrect syntax '@StartingAccountNumber'. Expecting '(', or SELECT"

Some searching around the internet has given me a potential solution:

DECLARE @Statement VARCHAR(200)
SET @Statement = 'ALTER TABLE BA 
ADD ES INT IDENTITY(' + CAST(@CurrentES AS VARCHAR) + ', 1);'
EXEC (@Statement)

However - I was informed this is not an option for this project. Is there any way to inject my paramater in a way similar to my original intent?

Solution

  • You can use DBCC CHECKIDENT to do it. First create the table, then reseed it using this command.

    CREATE TABLE #TempAccount (AccountNumber INT IDENTITY(1, 1))

Declare @StartingAccountNumber int
SET @StartingAccountNumber = 1000

DBCC CHECKIDENT
(
    #TempAccount, RESEED, @StartingAccountNumber

)

    You can read about it here: http://www.techrepublic.com/blog/the-enterprise-cloud/how-do-i-reseed-a-sql-server-identity-column/