Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X
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.
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