Execute stored procedure with an Output parameter

Question

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

User · Accepted Answer

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

User · Answer

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   parm  parmIN   parm1OUT  parmRET1 OUTPUT  parm2OUT  parmRET2 OUTPUT  SELECT  parmRET1 AS  parameter 1    parmRET2 AS  parameter 2  GO DROP PROCEDURE Myproc   Hope this helps

User · Answer

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

User · Answer

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

User · Answer

Return val from procedure  ALTER PROCEDURE testme  input  VARCHAR 10                           output VARCHAR 20  output AS   BEGIN       IF  input  gt    1          BEGIN             SET  output    i am back                RETURN          END   END  DECLARE  get VARCHAR 20    EXEC testme    1      get output  SELECT  get

User · Answer

How about this  It s extremely simplified     The SPROC below has an output parameter of  ParentProductID We want to select the value of the output of  ParentProductID into  MyParentProductID which is declared below   Here s the Code   declare  MyParentProductID int  exec p CheckSplitProduct  ProductId   4077   ParentProductID     MyParentProductID output  select  MyParentProductID

User · Answer

you can do this    declare  rowCount int exec yourStoredProcedureName  outputparameterspOf    rowCount output

User · Answer

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

User · Answer

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

User · Answer

With this query you can execute any stored procedure With or Without output parameter    DECLARE  temp varchar 100    EXEC my sp      parameter1   1        parameter2   2        parameter3    temp output        parameter4   3        parameter5   4 PRINT  temp      Here datatype of  temp should be same as  parameter3 within SP    Hope this helps

User · Answer

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

User · Answer

Please check below example to get output variable value by executing a stored procedure        DECLARE  return value int       Ouput1 int       Ouput2 int       Ouput3 int  EXEC     return value    Your Sp Name           Param1   value1           Ouput1    Ouput1 OUTPUT           Ouput2    Ouput2 OUTPUT           Ouput3    Ouput3 OUTPUT  SELECT   Ouput1 as N  Ouput1            Ouput2 as N  Ouput2            Ouput3 as N  Ouput3

User · Answer

I m using output parameter in SQL Proc and later I used this values in resultset

User · Answer

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

[sql-server] Execute stored procedure with an Output parameter?

Examples related to sql-server

Examples related to stored-procedures