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

问题描述

我有 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

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