Forum Discussion
Using a Sequence
Consider this table:
CREATE TABLE [Events](
[EventID] [int] NULL,
<< Other columns >>and this Sequence:
CREATE SEQUENCE [NewEventID]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CACHE and this Stored Procedure;
CREATE PROCEDURE [Insert_Event]
<< Parameters >>
AS
BEGIN
INSERT INTO
[Events]
(
EventID,
<< Other fields >>
)
VALUES
(
NEXT VALUE FOR NewEventID,
<< Other fields >>
)
END
GOWhen I run this procedure, 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.
None of those conditions are true so why am I getting this error message?
12 Replies
Maybe I'm misunderstanding your need but why would you not use an Identity field?
Because I load 300+ entries into the Events table every time it gets loaded, then those entries are deleted when the app terminates. I don't need an EventID for those entries so using an identity column makes no sense.
There are more limitations than the message mentioned, This article descripted https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16#limitations-and-restrictions
Since we can't see the context that you called sp, guess there might be having SET ROWCOUNT ON or something else.
rodgerkongI ran Example E in the page for "NEXT VALUE FOR" and got the same error message so the problem has nothing to do with my query. Now I am really at a loss....
- That's weird, did you check the permissions? Could you create a new db, grant db_owner role to do the test? And could you post whole code that you tested fail.
rodgerkongI think my OP was a bit misleading where I mentioned "Stored Procedure" I get the noted error message when I try to CREATE it. That is, when I run the procedure in the last code block.
I did try setting ROWCOUNT = 0 but that didn't change the result.
I've looked at the useful link you provided but I still can't identify what may be causing the error since I'm trying to create a stored procedure, not trying to run one. But I'll go over the possible causes again to see if something looks relevant.
