Generating a Sequential Number from SQL SERVER

Jes Sie

I'm making a sequence number form Certificate of Insurance booklet given to the agents so that the CI numbers can be tracked. My code is attached below:

CREATE PROCEDURE [dbo].[CI_SEQUENTIAL_Number]
(
@StartingNumber int,
@EndingNumber int,
@Header int
)
AS
DECLARE @start int
DECLARE @end int
SELECT @start = @StartingNumber, @end = @EndingNumber
WHILE @start <= @end
begin
INSERT INTO dbo.tblSeriesDetails
(SeriesNumbers)
VALUES(@start)
SET @start = @start + 1
where SeriesHeader_Id = @Header error in this area.
END

without the where clause, it is running but when I insert the where clause i got an error "

Msg 156, Level 15, State 1, Procedure SEQUENTIAL, Line 25

Incorrect syntax near the keyword 'where'." Still newbie and still acquiring knowledge in sql server. Thanks for any help.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted answer
You cannot use where clause with insert statement.

Insert statement would be like this
INSERT INTO dbo.tblSeriesDetails (SeriesNumbers) VALUES (@start)
To update an existing row, you have to use UPDATE statement like
Update tblSeriesDetails set SeriesNumbers = @start where SeriesHeader_id = @Header
Correcting the statement will fix the problem
Jes Sie9y ago
I got it. I altered my procedure and included the SeriesHeader_Id in the insert.
ALTER PROCEDURE [dbo].[CI_SEQUENTIAL_Number]
    (
        @StartingNumber int,
        @EndingNumber int,
        @SeriesHeader_Id int,
    )
AS

DECLARE @start int
DECLARE @end int

SELECT @start = @StartingNumber, @end = @EndingNumber

    WHILE @start <= @end
        begin

        INSERT INTO dbo.tblSeriesDetails

                (SeriesNumbers, SeriesHeader_Id )

        VALUES(@start, @SeriesHeader_Id )

        SET @start = @start + 1

        END
Bryian Tan9y ago
Why not let the SQl handle the auto increment by setting the SeriesHeader_Id to Identity Column type?

This post explain the option to recreate the table with identity and also option to update existing table to use identity.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032/how-to-alter-column-to-identity11?forum=transactsql
Rajeev Punhani9y ago
Hi,

In an insert statement you wouldn't have an existing row to do a where claues on? .Could you please explain what exactly you are trying to achieve and what is your table structure.