使用输出参数执行存储过程?

I have a stored procedure that I am trying to test. I am trying to test it through SQL Management Studio. In order to run this test I enter ...

我有一个正在测试的存储过程。我正在尝试通过SQL Management Studio测试它。为了运行这个测试，我输入…

exec my_stored_procedure 'param1Value', 'param2Value'

The final parameter is an output parameter. However, I do not know how to test a stored procedure with output parameters.

最后一个参数是一个输出参数。但是，我不知道如何用输出参数来测试存储过程。

How do I run a stored procedure with an output parameter?

如何使用输出参数运行存储过程?

11 个解决方案

#1

The easy way is to right-click on the procedure in Sql Server Management Studio(SSMS),

简单的方法是右键单击Sql Server Management Studio(SSMS)中的过程，

select execute stored procedure...

选择执行存储过程…

and add values for the input parameters as prompted.

并按提示添加输入参数的值。

SSMS will then generate the code to run the proc in a new query window, and execute it for you. You can study the generated code to see how it is done.

然后，SSMS将生成代码，在新的查询窗口中运行proc，并为您执行。您可以研究生成的代码，看看它是如何完成的。

#2

you can do this :

你可以这样做:

declare @rowCount int
exec yourStoredProcedureName @outputparameterspOf = @rowCount output

#3

Return val from procedure

从程序返回瓦尔

ALTER PROCEDURE testme @input  VARCHAR(10),
                       @output VARCHAR(20) output
AS
  BEGIN
      IF @input >= '1'
        BEGIN
            SET @output = 'i am back';

            RETURN;
        END
  END

DECLARE @get VARCHAR(20);

EXEC testme
  '1',
  @get output

SELECT @get 

#4

Check this, Where first two parameters are input parameters and 3rd one is Output parameter in Procedure definition.

检查一下，前两个参数是输入参数，第三个参数是过程定义中的输出参数。

DECLARE @PK_Code INT;
EXEC USP_Validate_Login  'ID', 'PWD', @PK_Code OUTPUT
SELECT @PK_Code

#5

From http://support.microsoft.com/kb/262499

从http://support.microsoft.com/kb/262499

Example:

例子:

CREATE PROCEDURE Myproc

@parm varchar(10),
**@parm1OUT varchar(30) OUTPUT**,
**@parm2OUT varchar(30) OUTPUT**
AS
  SELECT @parm1OUT='parm 1' + @parm
 SELECT @parm2OUT='parm 2' + @parm

GO

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)

SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                         @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                  @parm1OUT varchar(30) OUTPUT,
                  @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
GO
DROP PROCEDURE Myproc

Hope this helps!

希望这可以帮助!

#6

Procedure Example :

Create Procedure [dbo].[test]
@Name varchar(100),
@ID int Output   
As  
Begin   
SELECT @ID = UserID from tbl_UserMaster where  Name = @Name   
Return;
END     

How to call this procedure

Declare @ID int    
EXECUTE [dbo].[test] 'Abhishek',@ID OUTPUT   
PRINT @ID

#7

How about this? It's extremely simplified:

这个怎么样?它非常简单:

1. The SPROC below has an output parameter of @ParentProductID

   下面的SPROC具有@ParentProductID的输出参数

2. We want to select the value of the output of @ParentProductID into @MyParentProductID which is declared below.

   我们想要选择@ParentProductID输出到@MyParentProductID的值，如下所示。

3. Here's the Code:

   这是代码:

   declare @MyParentProductID int
   
   exec p_CheckSplitProduct @ProductId = 4077, @ParentProductID =  @MyParentProductID output
   
   select @MyParentProductID
   

#8

>Try this its working fine for the multiple output parameter:

>对多个输出参数进行测试，效果良好:

CREATE PROCEDURE [endicia].[credentialLookup]
@accountNumber varchar(20),
@login varchar(20) output,
@password varchar(50) output
AS
BEGIN
SET NOCOUNT ON;
SELECT top 1 @login = [carrierLogin],@password = [carrierPassword]
  FROM [carrier_account] where carrierLogin = @accountNumber
  order by clientId, id
END

Try for the result: 
SELECT *FROM [carrier_account] 
DECLARE @login varchar(20),@password varchar(50)
exec [endicia].[credentialLookup] '588251',@login OUTPUT,@password OUTPUT
SELECT 'login'=@login,'password'=@password

#9

First, declare the output variable:

首先，声明输出变量:

DECLARE @MyOutputParameter INT;

Then, execute the stored procedure, and you can do it without parameter's names, like this:

然后，执行存储过程，可以不使用参数名进行存储，如下所示:

EXEC my_stored_procedure 'param1Value', @MyOutputParameter OUTPUT

or with parameter's names:

或参数的名字:

EXEC my_stored_procedure @param1 = 'param1Value', @myoutput = @MyOutputParameter OUTPUT

And finally, you can see the output result by doing a SELECT:

最后，通过选择可以看到输出结果:

SELECT @MyOutputParameter 

#10

CREATE PROCEDURE DBO.MY_STORED_PROCEDURE
(@PARAM1VALUE INT,
@PARAM2VALUE INT,
@OUTPARAM VARCHAR(20) OUT)
AS 
BEGIN
SELECT * FROM DBO.PARAMTABLENAME WHERE PARAM1VALUE=@PARAM1VALUE
END

DECLARE @OUTPARAM2 VARCHAR(20)
EXEC DBO.MY_STORED_PROCEDURE 1,@OUTPARAM2 OUT
PRINT @OUTPARAM2

#11

Here is the stored procedure

这是存储过程

create procedure sp1
(
@id as int,
@name as nvarchar(20) out
)
as
begin
select @name=name from employee where id=@id
end

And here is the way to execute the procedure

这就是执行过程的方法

 declare @name1 nvarchar(10)
    exec sp1 1,@name1 out
    print @name1