Create sequence inside stored procedure

Question

CREATE OR ALTER PROCEDURE spTest
@DeezNutz varchar(15)
AS
BEGIN

DECLARE
@sequence varchar(25) = 'seq_transaction' + @DeezNutz

PRINT @sequence
-- prints the concatenated string ----> seq_transactionMary

CREATE SEQUENCE @sequence
START WITH 1
INCREMENT BY 1
-- Msg 102, Level 15, State 1, Procedure spTest, Line 13 [Batch Start Line 0]
-- Incorrect syntax near '@sequence'.

END

go
EXEC SPtEST 'Mary'

Please help, thank you

Answer 1

You can not use a variable for an object name, that's not allowed.

The only way round is dynamic SQL: https://www.sommarskog.se/dynamic_sql.html

Why do you want to create sequences by a stored procedure?

Answer 2

It seems that you must use a dynamic statement, for example:

declare @sql as varchar(max) = concat(
'CREATE SEQUENCE ', quotename(@sequence), '
START WITH 1
INCREMENT BY 1' )

exec (@sql)

Answer 3

Hi @Ben ,

Welcome to the microsoft TSQL Q&A forum!

Please also refer to the following questions:
sql server: Procedure to create sequence

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

You should probably look at ROW_NUMBER() instead of trying to use a sequence.

Answer 5

Hello OlafHelper:

My apologies for my late reply. Priority change....
A transaction sequence is one of many steps in opening a
new client account.
The code I posted is a small part of of the entire transaction
contents.
Thank you for your help