在SQL server中的另一个存储过程中执行一个存储过程

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中的另一个存储过程中执行存储过程?如何传递第二个过程的参数?

6 个解决方案

#1

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中定义相同的列返回值。

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 Id and Name where type of id is int and name is of varchar(64) type.

假设第二个sp返回Id和名称，其中Id类型为int, Name类型为varchar(64)。

now, if you want to select these values in first sp then create a temporary table variable and insert values into it:

现在，如果您想在sp中选择这些值，那么创建一个临时表变量，并将值插入其中:

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.

希望，这消除了你所有的疑虑。

#2

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

如前所述，我们可以从另一个存储过程调用一个存储过程

#3

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

#4

You can call User-defined Functions in a stored procedure alternately

您可以在存储过程中交替调用用户定义的函数

this may solve your problem to call stored procedure

这可以解决调用存储过程的问题

#5

Your sp_test: Return fullname

你的sp_test:fullname返回

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;
...

#6

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查询中使用。

select dbo.GetRegAge(R.DateOfBirth, r.RegistrationDate) as Age,R.DateOfBirth,r.RegistrationDate from T_Registration R

#1