Create a stored procedure that uses a sequence

Question

I am attempting to create a stored procedure that inserts a new record into a table but the procedure to create the stored procedure yields an error.

Here are the steps to demonstrate the problem:

CREATE TABLE [Events]
(
    [EventID] [int] NOT NULL
) ON [PRIMARY]

EventID is NOT an IDENTITY, but will be generated using the following sequence.

CREATE SEQUENCE [NewEventID] 
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CACHE

These above steps cause no problem but when I run the following:

CREATE PROCEDURE [Insert_Event]
AS
BEGIN
    INSERT INTO [Events] (EventID)
    VALUES (NEXT VALUE FOR NewEventID)
END

I get this error message:

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

If I replace NEXT VALUE FOR NewEventID with any number, this runs without an error and does create the [Insert_Event] procedure.

None of those conditions listed in limitations and restrictions on this page are true so why am I getting this error message?

Does it crash when you create procedure or when you execute it? What client do you use when executing code? What does this setting looks like if you use ssms: dbblogger.com/post/… — siggemannen
– siggemannen, Commented Oct 30, 2024 at 14:32
Cannot reproduce, db<>fiddle. What code aren't you showing us? Are you doing something like SET ROWCOUNT 1; before invoking your stored procedure? — AlwaysLearning
– AlwaysLearning, Commented Oct 30, 2024 at 14:41
Is a bit unnecessary that this breaks the creation of the proc. As the value of this setting at proc creation time has no bearing on the value it will have at run time dbfiddle.uk/B7Lprfsu — Martin Smith
– Martin Smith, Commented Oct 30, 2024 at 15:02
I am curious why this and not stackoverflow.com/a/6777807/125981 or if you just need numbered rows some answers in here stackoverflow.com/q/37829072/125981 — Mark Schultheiss
– Mark Schultheiss, Commented Oct 30, 2024 at 15:07
Probably the runtime value of rowcount is used during compilation and it doesn't figure out that it doesn't matter in create proc. @MartinSmith do you know how to figure out the currect SET ROWCOUNT value by code? I couldn't find a way — siggemannen
– siggemannen, Commented Oct 30, 2024 at 15:12

Dale K · Accepted Answer · 2025-06-26 23:36:39Z

1

Try setting SET ROWCOUNT to zero - like SET ROWCOUNT 0; and then recreating it. Like below

SET ROWCOUNT 0;
GO

CREATE PROCEDURE [Insert_Event]
AS
BEGIN
    INSERT INTO [Events] (EventID)
    VALUES (NEXT VALUE FOR NewEventID);
END;

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Oct 30, 2024 at 14:39

pumpkin

5662 silver badges13 bronze badges

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

1 Answer 1