Execute stored procedure with an Output parameter?

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 …

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?

C# Oracle execute stored procedure with output parameter

Situation: I’m trying to run a stored procedure that has an output parameter, which I need to catch. I use C# 3.5 and the OracleClient with an OleDbConnection. Research: I’ve been looking around for o

How can we define output parameter size in stored procedure?

How can we define output parameter size in stored procedure?

Calling Oracle stored procedure with output parameter from SQL Server

I have an Oracle linked server in SQL Server 2008 R2. I need to execute Oracle stored procedures (with output parameter in first, and input parameter in second procedure): CREATE OR REPLACE PROCEDURE

Getting output parameter from stored procedure

I’m trying to get the stored procedure to fire and return the output parameter as well as the result set. Currently I just get the result set array with a balnk variable where the output parameter sho

Initializing stored procedure output parameter

I have a simple SQL Server stored procedure: ALTER PROCEDURE GetRowCount ( @count int=0 OUTPUT ) AS Select * from Emp where age>30; SET @[email protected][email protected]@ROWCOUNT; RETURN I am trying to initialize a

SSIS Execute SQL Stored Procedure output parameter type mismatch

I’m getting the following error on a SSIS package: [Execute SQL Task] Error: Executing the query EXEC [spGetFileId] @zFileName, @Id failed with the following error: The type of the value being ass

Handle stored procedure output parameter in codeigniter

I have a stored procedure in mysql with a output parameter and i need to get the result to my php which is used codeigniter The Register mode public function registerUser($data){ $sql = call registe

Stored procedure with output parameter

Trying to create a Stored procedure that will give the required no. of records and also the total no. of records in that table. The ‘@Query’ part gives result when I execute it separately. So there’s

Calling an Oracle stored procedure that uses an output parameter from Microsoft SQL Server

I am trying to execute a stored procedure on an Oracle 10g server, which returns its results in an output parameter, from a SQL Server 2008 query. The Oracle stored procedure declaration looks like th

.NET CLR Stored procedure OUTPUT Parameter

I am creating a CLR stored procedure in VB.NET (.NET 3.5,SQLServer 2005). The procedure has 4 BYVal parameter and 4 BYRef parameters. It compiles and deploys correctly into SQL Server and a Stored pro

Answers

From 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,
@[email protected],
@[email protected] OUTPUT,@[email protected] OUTPUT

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

Hope this helps!

The easy way is to right-click on the procedure in 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.

you can do this :

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

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 

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

How about this? It’s extremely simplified:

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

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

  3. Here’s the Code:

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

>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'[email protected],'password'[email protected]

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].[text] 'Abhishek',@ID OUTPUT   
PRINT @ID
CREATE PROCEDURE DBO.MY_STORED_PROCEDURE
(@PARAM1VALUE INT,
@PARAM2VALUE INT,
@OUTPARAM VARCHAR(20) OUT)
AS 
BEGIN
SELECT * FROM DBO.PARAMTABLENAME WHERE [email protected]
END

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