T-SQL get SELECTed value of stored procedure

Question

In T-SQL  this is allowed   DECLARE  SelectedValue int SELECT  SelectedValue   MyIntField FROM MyTable WHERE MyPrimaryKeyField   1   So  it s possible to get the value of a SELECT and stuff it in a variable  provided it s scalar  obviously    If I put the same select logic in a stored procedure   CREATE PROCEDURE GetMyInt AS SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField   1   Can I get the output of this stored procedure and stuff it in a variable   Something like   DECLARE  SelectedValue int SELECT  SelectedValue   EXEC GetMyInt    I know the syntax above is not allowed because I tried it

User · Answer

Try do this   EXEC  SelectedValue   GetMyInt

User · Answer

You d need to use return values   DECLARE  SelectedValue int  CREATE PROCEDURE GetMyInt   MyIntField int OUTPUT  AS SELECT  MyIntField   MyIntField FROM MyTable WHERE MyPrimaryKeyField   1   Then you call it like this   EXEC GetMyInt OUTPUT  SelectedValue

User · Answer

there are three ways you can use  the RETURN value  and OUTPUT parameter and a result set  ALSO  watch out if you use the pattern   SELECT  Variable column FROM table      if there are multiple rows returned from the query  your  Variable will only contain the value from the last row returned by the query   RETURN VALUE since your query returns an int field  at least based on how you named it   you can use this trick   CREATE PROCEDURE GetMyInt    Param int  AS DECLARE  ReturnValue int  SELECT  ReturnValue MyIntField FROM MyTable WHERE MyPrimaryKeyField    Param RETURN  ReturnValue GO   and now call your procedure like   DECLARE  SelectedValue int          Param         int SET  Param 1 EXEC  SelectedValue   GetMyInt  Param PRINT  SelectedValue   this will only work for INTs  because RETURN can only return a single int value and nulls are converted to a zero   OUTPUT PARAMETER you can use an output parameter   CREATE PROCEDURE GetMyInt    Param     int    OutValue  int OUTPUT  AS SELECT  OutValue MyIntField FROM MyTable WHERE MyPrimaryKeyField    Param RETURN 0 GO   and now call your procedure like   DECLARE  SelectedValue int          Param         int SET  Param 1 EXEC GetMyInt  Param   SelectedValue OUTPUT PRINT  SelectedValue    Output parameters can only return one value  but can be any data type  RESULT SET for a result set make the procedure like   CREATE PROCEDURE GetMyInt    Param     int  AS SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField    Param RETURN 0 GO   use it like   DECLARE  ResultSet table  SelectedValue int  DECLARE  Param int SET  Param 1 INSERT INTO  ResultSet  SelectedValue      EXEC GetMyInt  Param SELECT   FROM  ResultSet    result sets can have many rows and many columns of any data type

User · Answer

There is also a combination  you can use a return value with a recordset   --Stored Procedure--  CREATE PROCEDURE  TestProc   AS BEGIN      DECLARE  Temp TABLE                Name  VARCHAR 50             INSERT INTO  Temp VALUES   Mark        INSERT INTO  Temp VALUES   John        INSERT INTO  Temp VALUES   Jane        INSERT INTO  Temp VALUES   Mary         -- Get recordset     SELECT   FROM  Temp      DECLARE  ReturnValue INT     SELECT  ReturnValue   COUNT  Name   FROM  Temp      -- Return count     RETURN  ReturnValue  END   --Calling Code--  DECLARE  SelectedValue int EXEC  SelectedValue    TestProc    SELECT  SelectedValue   --Results--

[tsql] T-SQL get SELECTed value of stored procedure

Examples related to tsql

Examples related to stored-procedures

Examples related to select