question

Ben-7644 avatar image
0 Votes"
Ben-7644 asked ErlandSommarskog commented

Create sequence inside stored procedure

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

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

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




sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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)


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And not only do Ben need dynamic SQL to create the sequence. He will also need it when he is going pick values from the sequence. I don't think this will be fun. Maybe it's time to reconsider the design?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Ben-7644,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ben-7644 avatar image
0 Votes"
Ben-7644 answered ErlandSommarskog commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How many accounts do you expect to have?

Why do you want to use sequences? Wouldn't it be easier to roll your own?

Do you have a requirement that the numbers for an account are contiguous?

I'm very much in doubt that using one sequence per account is the correct thing to do.

0 Votes 0 ·
Ben-7644 avatar image Ben-7644 ErlandSommarskog ·

Now that this project has my full attention, I am rethinking
the idea of a seqence for each account, that I inherited.
what do you mean "roll your own"?

0 Votes 0 ·

what do you mean "roll your own"?

That you get the current max value for the account and add one to get the next value. Or something of that kind.

Note that I have no idea what you intended to use these sequences for. I can only guess. So by necessity my answer has to be vague.

0 Votes 0 ·