在存储过程中执行存储过程 [英] Executing a Stored Procedure within a stored Procedure
本文介绍了在存储过程中执行存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
.First存储过程
创建 程序 [dbo]。[Check_IF_Person_Exist]
@ FirstName nvarchar ( 50 ),
@ LastName nvarchar ( 50 ),
@ Guid nvarchar ( 50 ),
@ intPersonID int 输出
AS
BEGIN
IF EXISTS ( SELECT ID FROM ITTESI_AssetTracker_Person WHERE Guid = @ Guid )
BEGIN
- 将现有PersonID分配给输出参数@intPersonID
SET @ intPersonID =( SELECT [ID] FROM AssetTracker_Person WHERE Guid = @ Guid )
结束
ELSE
BEGIN
INSERT INTO ITTESI_AssetTracker_Person VALUES ( @Guid , @ FirstName , @ LastName )
< span class =code-comment> - 将新生成的PersonID分配给输出参数@intPersonID
SET @ intPersonID = @@ IDENTITY
END
END
第一个存储过程已经有效。
第二个存储过程
创建 procedure InsertAssets1
@ Location int ,
@ LocationName nvarchar ( 50 ),
@ Manufacturer nvarchar ( 50 ) ,
@ Model nvarchar ( 50 ),
@ AssetTag nvarchar ( 50 ),
@AssignedDate nvarchar ( 50 ),
@ GUID int ,
@ FirstName nvarchar ( 50 ),
@ MiddleName nvarchar ( 50 ),
@ LastName nvarchar ( 50 ),
@ Condtion nvarchar (< span class =code-digit> 50 ),
@Notes nvarchar (最大)
作为
BEGIN
执行 Check_IF_Person_Exist @ FirstName , @ LastName , @ GUID
END
所以我的最终问题是,如何通过执行Check_IF_Person_Exist来获取返回的id,以及如何将其放入变量中,因为我将需要它用于另一个存储过程。
感谢您的帮助。
解决方案
查看这篇文章。我认为它对你有所帮助。
http://blog.sqlauthority.com/2013/04/07/sql-server-pass-one-stored-procedures-result-as-another-stored-procedures -parameter / [ ^ ]
http://stackoverflow.com/questions/12834838/calling-one-stored-procedure-within-另一个存储过程使用变量 [ ^ ]
Hey guys i need your help. I am going to execute multiple stored-procedures within a stored procedure, but for now, i need to know how to get the output value from one executed stored-procedure within a stored-procedure
.First Stored-Procedure
Create Procedure [dbo].[Check_IF_Person_Exist]
@FirstName nvarchar(50),
@LastName nvarchar (50),
@Guid nvarchar(50),
@intPersonID int OUTPUT
AS
BEGIN
IF EXISTS( SELECT ID FROM ITTESI_AssetTracker_Person WHERE Guid = @Guid)
BEGIN
-- Assign existing PersonID to output parameter @intPersonID
SET @intPersonID = ( SELECT [ID] FROM AssetTracker_Person WHERE Guid = @Guid)
END
ELSE
BEGIN
INSERT INTO ITTESI_AssetTracker_Person VALUES (@Guid,@FirstName,@LastName)
-- Assign newly generated PersonID to output parameter @intPersonID
SET @intPersonID = @@IDENTITY
END
END
first stored-procedure already works.
Second Stored-Procedure
Create procedure InsertAssets1
@Location int,
@LocationName nvarchar(50),
@Manufacturer nvarchar(50),
@Model nvarchar(50),
@AssetTag nvarchar(50),
@AssignedDate nvarchar(50),
@GUID int,
@FirstName nvarchar(50),
@MiddleName nvarchar(50),
@LastName nvarchar(50),
@Condtion nvarchar(50),
@Notes nvarchar(Max)
As
BEGIN
Exec Check_IF_Person_Exist @FirstName,@LastName,@GUID
END
So my ultimate question, is how do i get the id returned by executing Check_IF_Person_Exist and how do i put that in a variable because i will need it for another stored-procedure.
Thanks for your help.
解决方案
check this article.i think it will helpful for you.
http://blog.sqlauthority.com/2013/04/07/sql-server-pass-one-stored-procedures-result-as-another-stored-procedures-parameter/[^]
http://stackoverflow.com/questions/12834838/calling-one-stored-procedure-within-another-stored-procedure-using-variables-fro[^]
这篇关于在存储过程中执行存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文