SQL Server Stored Procedure for Insertion into Multiple Tables

Question

I have 2 tables, custloginand 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 that will be inserted into both tables

More precisely, paste in custloginwith custusername custpassword, which will return custidfor use as a foreign key for custinfo.

I searched a lot, but did not find a solution.

Answer 1

It will be something like below. You can use SCOPE_IDENTITY()to get the last autogenerated identifier with 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