在多个表中插入 SQL Server 存储过程

2022-01-09 00:00:00 sql insert sql-server stored-procedures

我有 2 个表，custlogin 和 custinfo:

I have 2 tables, custlogin and custinfo:

客户登录:

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

客户信息:

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

相关文章