[SOLVED] Create a stored procedure that uses a sequence

Create a stored procedure that uses a sequence

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?

Solution

did you try setting SET ROWCOUNT to zero? like SET ROWCOUNT 0; and then recreating it. so like below

SET ROWCOUNT 0;

GO

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