Using a Parameter As An IDENTITY starting number

Question

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?

I was instructed to not use dynamic SQL. More searching around the web makes it appear it may be the only way to get something like this to work it seems. — PWilliams0530, Jul 01 '14 at 19:25

score 2 · Accepted Answer · answered Jul 01 '14 at 19:25

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/

score 0 · Answer 2 · answered Jul 01 '14 at 19:47

It sounds like what you want to do is to generate a monotonic series of numbers, correct?

It is often useful to have a utility table around that contains a large range of continous integer values, something like this:

create table dbo.sequence ( id int not null primary key clustered )
go

set nocount on
go
declare @n  int = -1000000 
while ( @n < +1000000 )
begin
  insert dbo.sequence values ( @n )
  set @n = @n+1
end
go
set nocount on
go

You only have to do with once. Once you have such a source, things get easy:

CREATE TABLE #TempAccount ( AccountNumber int primary key clustered )

declare @starting_account_number int = 123456789
declare @count                   int = 10000

insert #TempAccount ( AccountNumber )
select @starting_account_number + offset.id
from dbo.sequence offset
where offset.id between 0 and @count-1

Using a Parameter As An IDENTITY starting number

2 Answers2