SQL Server 存储过程插入多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20963961/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server stored procedure to insert in multiple tables
提问by Sadiqabbas Hirani
I have 2 tables, custloginand custinfo:
我有 2 张桌子，custlogin并且custinfo：
custlogin:
custlogin：
custid int primary key auto notnull
custusename varchar(25)
custpassword varchar(50)
custinfo:
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 custloginwith custusername custpassword, which would return custidfor use as foreign key for custinfo.
更准确地说，插入custloginwith custusername custpassword，它将返回custid用作 的外键custinfo。
I have searched much but I didn't find any solution.
我已经搜索了很多，但没有找到任何解决方案。
回答by Szymon
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:
它会像下面这样。SCOPE_IDENTITY()在这种情况下，您可以使用此存储过程的范围获取最后一个自动生成的 ID：
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
