CODEWITHSUNDEEP
sql-serverfunctiont-sqlsql-server-2012
Luis Arturo Cabello Rangel
Luis Arturo Cabello Rangel

Reputation: 53

Use a Sequence in a function SQL Server

I'm trying to use a sequence inside a T-SQL function but T-SQL doesn't allow the use of Next Value for, and OPENROWSET doesn't allow me to do queries because of the security of the database.

CREATE FUNCTION dbo.newInvoice()
RETURNS varchar
AS
BEGIN
    DECLARE @NumberHold AS bigint

    SELECT 
        @NumberHold = NEXT VALUE FOR dbo.Ten_Seq

    RETURN 
        dbo.sfnSprintf('MyOmnyInvoice[%s]', CAST(@NumberHold as varchar), default);
END

Upvotes: 1

Views: 7241

Answers (2)

Swarathesh Addanki
Swarathesh Addanki

Reputation: 451

You cannot use NEXT VALUE FOR function in built-in functions.

Alternative use STORED PROCEDURE

CREATE PROCEDURE [dbo].[GetSeqNextValue3] (@NewNum bigint output) 

AS

BEGIN
  Declare  @SQL  Nvarchar(1000)

  Set @SQL  = 'SELECT @NewNum = Next Value for dbo.IdSequence'
  Exec sp_executesql @Sql,N'@NewNum bigint output',@NewNum output 


END 

DECLARE @id bigint
exec [dbo].[GetSeqNextValue3] @id output
select @id

Upvotes: 0

sqluser
sqluser

Reputation: 5672

You cannot use NEXT VALUE FOR function in built-in functions

According to MSDN

Limitations and Restrictions

In views, in user-defined functions, or in computed columns.

Upvotes: 6

Related Questions