CODEWITHSUNDEEP
sqlsql-server
sammy Simpson
sammy Simpson

Reputation: 1

SQL Server : How to do a NEXT VALUE FOR in a SELECT statement

I have created a sequence whose name is saved in a table called tableName. I want to create the table test with the sequence name in tableName.

CREATE TABLE Test
(
     T_ID INT default (NEXT VALUE FOR (Select x from tableName)
)

Upvotes: 0

Views: 1712

Answers (1)

Piotr Palka
Piotr Palka

Reputation: 3159

You will need to use dynamic SQL, if you wish to use a sequence name stored in a table as a part of a select. If you need to get numbers from sequence in a stored procedure, you can use procedure sp_sequence_get_range, it accepts sequence name as a parameter.

There is an example how to create sequence:

CREATE SEQUENCE dbo.tableSequence  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

Example for select:

SELECT NEXT VALUE FOR dbo.tableSequence FROM dbo.tableName

Example for default value of primary key:

CREATE TABLE dbo.tableName  
(  
    EventID int PRIMARY KEY CLUSTERED   
        DEFAULT (NEXT VALUE FOR dbo.tableSequence),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EventCode nvarchar(5) NOT NULL,  
    Description nvarchar(300) NULL  
) ;  
GO

You can learn more about TSQL sequnces at: https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-ver15

Upvotes: 1

Related Questions