从另一个存储过程 SQL Server 调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8858620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling stored procedure from another stored procedure SQL Server
提问by arturo r
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
表 1 的 SP 表 2 的 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 并在表 1 中插入 FK-ID
回答by Mark Kadlec
Simply call test2from test1like:
只需test2从test1如下调用：
EXEC test2 @newId, @prod, @desc;
Make sure to get @idusing SCOPE_IDENTITY(), which gets the last identity value inserted into an identity column in the same scope:
确保@id使用 SCOPE_IDENTITY()，它将最后一个标识值插入到同一范围内的标识列中：
SELECT @newId = SCOPE_IDENTITY()
回答by AdaTheDev
You could add an OUTPUT parameter to test2, and set it to the new id straight after the INSERT using:
您可以向 test2 添加一个 OUTPUT 参数，并在 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
回答by Sergio Rosas
First of all, if table2's idProduct is an identity, you cannot insert it explicitly until you set IDENTITY_INSERTon 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 productNameand productDescriptionand 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 table1with that ID
或者您已经拥有产品的 ID 并且您不需要调用SCOPE_IDENTITY()并且可以table1使用该 ID进行插入
