Use a Sequence in a function SQL Server

Question

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

What version of SQL Server? Your question says "New Value For" but your code says "Next Value For"? What's the actual error? Why do you need to use Openrowset? Please clarify. — Nick.Mc
– Nick.Mc, Commented May 5, 2015 at 22:58
SQL server 2012, I need the "OPENROWSET" in order to bypass the "Next Value For" restriction on the function, by the way is "Next Value For" excuse me for my typing error — Luis Arturo Cabello Rangel
– Luis Arturo Cabello Rangel, Commented May 5, 2015 at 23:06
Is there any reason you need to do this stuff in a function? You could put this in a stored procedure instead but of course there are limitations when calling them. — Nick.Mc
– Nick.Mc, Commented May 5, 2015 at 23:16
The idea behind this is use this function with Fluent Nhibernate, so I can manage all the database in code — Luis Arturo Cabello Rangel
– Luis Arturo Cabello Rangel, Commented May 5, 2015 at 23:21
Basically - there's nothing you can do in a user-defined function that has side effects. No newid(), no sequences, no getdate(), nothing that updates the database and nothing that will make the function return a different value on subsequent calls. — Clay
– Clay, Commented May 5, 2015 at 23:32

sqluser · Accepted Answer · 2015-05-06 00:10:51Z

6

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.

edited May 6, 2015 at 0:10

answered May 5, 2015 at 23:01

sqluser

5,7027 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Swarathesh Addanki · Accepted Answer · 2021-08-12 18:54:26Z

0

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

answered Aug 12, 2021 at 18:54

Swarathesh Addanki

4511 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Use a Sequence in a function SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related