CODEWITHSUNDEEP
sql-serverprocedure
Medivh
Medivh

Reputation: 23

Stored procedure with conditional insert

I am trying to write a procedure with an If statement.

This is my database

DataBase

My idea is: when we have same ComandmentNumber, StudentNumber must increase by 1, but when we have new ComandmentNumber, StudentNumber must start from 1.

Example:

enter image description here

Here is my procedure in SQL Server 

CREATE PROCEDURE InsertIntoDefeatsGraduates
    @ComandmentNumber VARCHAR(100),
    @StudentFakNumber INT
AS
BEGIN
    DECLARE @StudentNumber INT 

    IF EXISTS (SELECT * FROM dbo.[Defeats-Graduates] 
               WHERE ComandmentNumber = @ComandmentNumber) 
    BEGIN
        SET @StudentNumber += 1

        INSERT INTO dbo.[Defeats-Graduates] (ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
    END
    ELSE
    BEGIN
        SET @StudentNumber = 1

        INSERT INTO dbo.[Defeats-Graduates](ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
    END
END

But when I try to execute this, I get this error:

Cannot insert the value NULL into column 'StudentNumber', table 'Test.dbo.Defeats-Graduates'; column does not allow nulls. INSERT fails.

Upvotes: 1

Views: 2218

Answers (2)

marc_s
marc_s

Reputation: 754963

You never initialize your @StudentNumber - you just declare it - then it's NULL - and adding +1 to NULL still remains NULL - so in the end, you're trying to insert NULL into your table

-- at this point, @StudentNumber is NULL 
DECLARE @StudentNumber INT 

IF EXISTS (SELECT * FROM dbo.[Defeats-Graduates] 
           WHERE ComandmentNumber = @ComandmentNumber) 
BEGIN
    -- trying to increment a NULL value still results in NULL
    SET @StudentNumber += 1

    -- here you're about to insert NULL into "StudentNumber"
    INSERT INTO dbo.[Defeats-Graduates] (ComandmentNumber, StudentNumber, StudentFakNumber) 
    VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)

What you need to do is read the actual value in the case it exists:

IF EXISTS (SELECT * FROM dbo.[Defeats-Graduates] 
           WHERE ComandmentNumber = @ComandmentNumber) 
BEGIN
    -- get the highest value and increment by one
    SELECT @StudentNumber = MAX(StudentNumber) + 1
    FROM dbo.[Defeats-Graduates] 
    WHERE ComandmentNumber = @ComandmentNumber;

BE AWARE: the SELECT MAX()+1 approach is very vulnerable to producing duplicate values, if you have a busy system with multiple clients working and inserting data at the same time. Whenever possible, you should use a proper database-provided mechanism instead - something like a SEQUENCE in SQL Server or something similar, that is designed and built to provide unique values, even under intense work loads.

Upvotes: 3

Shawn Xiao
Shawn Xiao

Reputation: 590

You have no assignment statements for @StudentNumber in the first condition, You can try this:

CREATE PROCEDURE InsertIntoDefeatsGraduates
    @ComandmentNumber VARCHAR(100),
    @StudentFakNumber INT
AS
BEGIN
    DECLARE @StudentNumber INT 
    SELECT @StudentNumber = StudentNumber  FROM dbo.[Defeats-Graduates] 
               WHERE ComandmentNumber = @ComandmentNumber
    IF ISNULL(@StudentNumber, 0) > 0
      BEGIN
        SET @StudentNumber += 1

        INSERT INTO dbo.[Defeats-Graduates] (ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
      END
    ELSE
      BEGIN
        SET @StudentNumber = 1

        INSERT INTO dbo.[Defeats-Graduates](ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
      END
    END

Upvotes: 1

Related Questions