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

问题描述

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

want to write a stored procedure which will insert into both tables

推荐答案

CREATE PROCEDURE usp_ins_cust(@cust_un varchar, 
                              @cust_pw varchar, 
                              @cust_fn varchar, 
                              @cust_ln varchar,
                              @cust_addr varchar)
AS
BEGIN TRY
     BEGIN TRANSACTION
           INSERT INTO custlogin(custusename, custpassword) values (@cust_un, @cust_pw)
           INSERT INTO custinfo(custid, custfirstname, custlastname, custaddress) 
            values (SCOPE_IDENTITY(), @cust_fn, @cust_ln, @cust_addr)
     COMMIT
 END TRY
 BEGIN CATCH
  ROLLBACK
 END CATCH

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