CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jdiehl
Jdiehl

Reputation: 223

How to fix: Cannot insert the value NULL into column, insert failed

For school I'm busy writing a stored procedure on my database. Now I get an error when I try to execute it.

I already tried looking on stack overflow for fixes but could not find anything that helps me.

create procedure spNieuweBestelling
    (@medewerkerid int,
     @productid int,
     @aantal_producten int)
as
begin
    declare @bestellingsid int
    declare @besteldatum date = getdate()

    select @bestellingsid = max(@bestellingsid) + 1
    from bestelling b;

    begin transaction
        insert into bestelling 
        values (@bestellingsid, @medewerkerid, @besteldatum)

        insert into productbestelling 
        values (@productid, @bestellingsid, @aantal_producten)

        if @@ERROR <> 0
        begin
            rollback
            raiserror ('error tijdens de 2de insert',16,1)
            return
        end
    end
    commit

exec spNieuweBestelling 2,2,200

Screenshot of the tables: https://prnt.sc/npqhiz

I expect that this procedure will insert into this 2 tables but it keeps raising this error.

Upvotes: 0

Views: 1686

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

I think you made a typo:

select @bestellingsid = max(@bestellingsid) + 1
    from bestelling b;

Should probably be:

select @bestellingsid = max(bestellingsid) + 1
    from bestelling b;

But why don't you just use an IDENTITY?

Also, since this is for school; When you insert into a table always list the columns:

INSERT INTO productbestelling (productid, bestellingsid, aantal_producten)

Upvotes: 1

Related Questions