Create 99 sequences in MS SQL

[VasiliyRepecki]

Hi.

I need create 99 sequences that differ by number. I'm using MS SQL.

I tried to use something like that:

DECLARE @intFlag INT

SET @intFlag = 1

WHILE (@intFlag <= 99)

BEGIN

CREATE TABLE wt_sequence_KOIID_ + @intFlag + _seq (dummy CHAR(1), value BIGINT IDENTITY(1,1))

go

CREATE PROCEDURE wt_get_next_sequence_KOIID_ + @intFlag + _seq @returnValue BIGINT OUTPUT

AS

INSERT wt_sequence_KOIID_ + @intFlag + _seq (dummy) VALUES ('x')

SELECT @returnValue = SCOPE_IDENTITY()

go

SET @intFlag = @intFlag + 1

END

go

But there are too many errors. Can anyone help me and give correct sql code?

Thanks.

[ChrisSpartz]

I think this will work if you first store the CREATE TABLE and CREATE PROCEDURE commands as string variables, and then run EXEC on those variables. Try something like the below:

DECLARE @intFlag INT,@table nvarchar(300), @createTable nvarchar(300),@proc nvarchar(300), @createProc nvarchar(300)

SET @intFlag = 1

WHILE (@intFlag <= 99)

BEGIN

SET @table = 'wt_sequence_KOIID_' + cast(@intFlag as nvarchar) + '_seq'

SET @createTable = 'CREATE TABLE ' + @table + '(dummy CHAR(1), value BIGINT IDENTITY(1,1))'

EXEC(@createTable)

SET @proc = 'wt_get_next_sequence_KOIID_' + cast(@intFlag as nvarchar) + '_seq'

SET @createProc = 'CREATE PROCEDURE ' + @proc + '(@returnValue BIGINT OUTPUT)

AS

INSERT ' + @table + ' (dummy) VALUES (''x'')

SELECT @returnValue = SCOPE_IDENTITY()'

EXEC(@createProc)

SET @intFlag = @intFlag + 1

END

go