SQL Server存储过程可插入多个表 [英] SQL Server stored procedure to insert in multiple tables

问题描述

我有2个表，custlogin和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

更准确地说，用custusername custpassword插入custlogin，这将返回custid用作custinfo的外键.

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.

推荐答案

如下所示.在这种情况下，您可以使用SCOPE_IDENTITY()获取最后一个自动生成的ID以及此存储过程的作用域:

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

这篇关于SQL Server存储过程可插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！