在 SQL Server 中的另一个存储过程中执行一个存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15802511/
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
Execute a stored procedure in another stored procedure in SQL server
提问by Roys
How can i execute a stored procedure in another stored procedure in SQL server? How will I pass the parameters of the second procedure.?
如何在 SQL Server 的另一个存储过程中执行一个存储过程？我将如何传递第二个过程的参数。？
回答by Ken Clark
If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do:
如果您只想通过第二个 SP 执行某些特定操作并且不需要从 SP 返回值，那么只需执行以下操作：
Exec secondSPName @anyparams
Else, if you need values returned by your second SP inside your first one, then create a temporary table variable with equal numbers of columns and with same definition of column return by second SP. Then you can get these values in first SP as:
否则，如果您需要第一个 SP 中的第二个 SP 返回的值，则创建一个具有相同列数和第二个 SP 返回的列定义相同的临时表变量。然后你可以在第一个 SP 中获得这些值：
Insert into @tep_table
Exec secondSPName @anyparams
Update:
更新：
To pass parameter to second sp, do this:
要将参数传递给第二个 sp，请执行以下操作：
Declare @id ID_Column_datatype
Set @id=(Select id from table_1 Where yourconditions)
Exec secondSPName @id
Update 2:
更新 2：
Suppose your second sp returns Idand Namewhere type of idis intand nameis of varchar(64)type.
假设您的第二个 sp 返回Id并且Name其中类型id是int和name是varchar(64)类型。
now, if you want to select these values in first sp then create a temporary tablevariable and insert values into it:
现在，如果要在第一个 sp 中选择这些值，则创建一个临时table变量并将值插入其中：
Declare @tep_table table
(
Id int,
Name varchar(64)
)
Insert into @tep_table
Exec secondSP
Select * From @tep_table
This will return you the values returned by second SP.
这将返回第二个 SP 返回的值。
Hope, this clear all your doubts.
希望，这可以消除您所有的疑虑。
回答by Sagar Hirapara
Suppose you have one stored procedure like this
假设你有一个这样的存储过程
First stored procedure:
第一个存储过程：
Create PROCEDURE LoginId
@UserName nvarchar(200),
@Password nvarchar(200)
AS
BEGIN
DECLARE @loginID int
SELECT @loginID = LoginId
FROM UserLogin
WHERE UserName = @UserName AND Password = @Password
return @loginID
END
Now you want to call this procedure from another stored procedure like as below
现在你想从另一个存储过程调用这个过程，如下所示
Second stored procedure
第二个存储过程
Create PROCEDURE Emprecord
@UserName nvarchar(200),
@Password nvarchar(200),
@Email nvarchar(200),
@IsAdmin bit,
@EmpName nvarchar(200),
@EmpLastName nvarchar(200),
@EmpAddress nvarchar(200),
@EmpContactNo nvarchar(150),
@EmpCompanyName nvarchar(200)
AS
BEGIN
INSERT INTO UserLogin VALUES(@UserName,@Password,@Email,@IsAdmin)
DECLARE @EmpLoginid int
**exec @EmpLoginid= LoginId @UserName,@Password**
INSERT INTO tblEmployee VALUES(@EmpName,@EmpLastName,@EmpAddress,@EmpContactNo,@EmpCompanyName,@EmpLoginid)
END
As you seen above, we can call one stored procedure from another
正如你在上面看到的，我们可以从另一个调用一个存储过程
回答by Vishal Suthar
Yes, you can do that like this:
是的，你可以这样做：
BEGIN
DECLARE @Results TABLE (Tid INT PRIMARY KEY);
INSERT @Results
EXEC Procedure2 [parameters];
SET @total 1;
END
SELECT @total
回答by meer
You can call User-defined Functions in a stored procedure alternately
您可以交替调用存储过程中的用户定义函数
this may solve your problem to call stored procedure
这可能会解决您调用存储过程的问题
回答by alditis
Your sp_test: Return fullname
你的sp_test：返回全名
USE [MY_DB]
GO
IF (OBJECT_ID('[dbo].[sp_test]', 'P') IS NOT NULL)
DROP PROCEDURE [dbo].sp_test;
GO
CREATE PROCEDURE [dbo].sp_test
@name VARCHAR(20),
@last_name VARCHAR(30),
@full_name VARCHAR(50) OUTPUT
AS
SET @full_name = @name + @last_name;
GO
In your sp_main
在你的sp_main
...
DECLARE @my_name VARCHAR(20);
DECLARE @my_last_name VARCHAR(30);
DECLARE @my_full_name VARCHAR(50);
...
EXEC sp_test @my_name, @my_last_name, @my_full_name OUTPUT;
...
回答by Hrishikesh shivacharan
Yes , Its easy to way we call the function inside the store procedure.
是的，我们在存储过程中调用函数很容易。
for e.g. create user define Age function and use in select query.
例如，创建用户定义年龄函数并在选择查询中使用。
select dbo.GetRegAge(R.DateOfBirth, r.RegistrationDate) as Age,R.DateOfBirth,r.RegistrationDate from T_Registration R
回答by Govinda Kudarpake
Procedure example:
程序示例：
Create PROCEDURE SP_Name
@UserName nvarchar(200),
@Password nvarchar(200)
AS
BEGIN
DECLARE @loginID int
--Statements for this Store Proc
--
--
--
--execute second store procedure
--below line calling sencond Store Procedure Exec is used for execute Store Procedure.
**Exec SP_Name_2 @params** (if any)
END
