SQL Server stored procedure to insert in multiple

Question

This question already has an answer here:

• How do I insert into two tables all at once in a stored procedure? [duplicate] 1 answer

I have 2 tables, custlogin and custinfo:

custlogin:

custid int primary key auto notnull
custusename varchar(25)
custpassword varchar(50)

custinfo:

custid foriegnkey custlogin.custid ondelete set NULL
custfirstname varchar(25)
custlastname  varchar(25)
custaddress   varchar(100)

I want to write a stored procedure which will insert into both tables

More precisely, insert into custlogin with custusername custpassword, which would return custid for use as foreign key for custinfo.

I have searched much but I didn't find any solution.

Answer 1

It will be something like below. You can use SCOPE_IDENTITY() to get the last autogenerated ID withing the scope which is this stored proc in this case:

create procedure NameOfYourProcedureHere
as
begin

    insert into custlogin(custusename, custpassword) 
        values ('','') -- put values here (from parameters?)

    insert into custinfo(custid, custfirstname, custlastname, custaddress)
        values (SCOPE_IDENTITY(), '', '', '')  -- put other values here (from parameters?)

end