从另一个存储过程SQL Server调用存储过程

I have 3 insert stored procedures each SP inserts data in 2 different tables

我有3个插入存储过程，每个SP在2个不同的表中插入数据

Table 1          Table 2                
idPerson         idProduct             
name             productName            
phoneNumber      productdescription     
FK-idProduct

SP for table 1 SP for table 2

对于表2，表1 SP的SP

create procedure test1                create procedure test2
WITH                                  WITH 
EXECUTE as caller                     EXECUTE as caller
AS                                    AS
declare                               declare

@idPerson int,                        @idProduct int,
@name varchar(20),                    @productName varchar(50),
@phone varchar(20)                    @productoDescription varchar(50)


  SET nocount on;                     SET nocount on;
    Begin                             Begin
      insert into table1(                insert into table2(
                idPerson,                          idProduct,
                name,                              productName,
                phone)                             productDescription)
          values(                            values(
                @idPerson,                         @idProduct,
                @name,                             @productName,
                @phone)                            @productDescription)
      end                               end

I need to call stored procedure test 2 from stored procedure test 1 and insert the FK-ID in the table 1

我需要从存储过程测试1调用存储过程测试2并将FK-ID插入表1中

3 个解决方案

#1

Simply call test2 from test1 like:

只需从test1调用test2，如：

EXEC test2 @newId, @prod, @desc;

Make sure to get @id using:

确保使用以下内容获取@id：

SELECT @newId = @@SCOPE_IDENTITY

#2

You could add an OUTPUT parameter to test2, and set it to the new id straight after the INSERT using:

您可以将一个OUTPUT参数添加到test2，并在INSERT之后使用以下命令将其设置为新的id：

SELECT @NewIdOutputParam = SCOPE_IDENTITY()

Then in test1, retrieve it like so:

然后在test1中，像这样检索它：

DECLARE @NewId INTEGER
EXECUTE test2 @NewId OUTPUT
-- Now use @NewId as needed

#3

First of all, if table2's idProduct is an identity, you cannot insert it explicitly until you set IDENTITY_INSERT on that table

首先，如果table2的idProduct是一个标识，则在该表上设置IDENTITY_INSERT之前，不能显式插入它

SET IDENTITY_INSERT table2 ON;

before the insert.

在插入之前。

So one of two, you modify your second stored and call it with only the parameters productName and productDescription and then get the new ID

因此，您可以修改第二个存储，并仅使用参数productName和productDescription调用它，然后获取新ID

EXEC test2 'productName', 'productDescription'
SET @newID = SCOPE_IDENTIY()

or you already have the ID of the product and you don't need to call SCOPE_IDENTITY() and can make the insert on table1 with that ID

或者您已经拥有该产品的ID，并且您不需要调用SCOPE_IDENTITY（）并且可以使用该ID在table1上进行插入

#1