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.
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
SET NOCOUNT ON;
SET XACT_ABORT ON;
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?)
SET NOCOUNT OFF;
SET XACT_ABORT OFF;
end
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments