Can stored procedures return/output a value like a function can?

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 29, 2019 at 4:09 pm

  #376880

  Sample code:

  
CREATE PROCEDURE ColorPopularity

  AS

  BEGIN

  SELECT

  Color, SUM(Quantity) AS QuantitySold
FROM LineItemDetails
GROUP BY Color
ORDER BY QuantitySold DESC

  END
  

  I see many similarities between stored procedures and functions.  Functions can return values. 

  1.  But can stored procedures return values?
  2.  In the example above, the stored procedure displays the results of a SELECT statement.  Do these records displayed by the SELECT statement count as a returned value?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 29, 2019 at 5:15 pm

  #2020491

  michael.leach2015 - Tuesday, January 29, 2019 4:09 PM

  Sample code:

  
CREATE PROCEDURE ColorPopularity


  AS
  

  BEGIN

  SELECT Color, SUM(Quantity) AS QuantitySold
  FROM LineItemDetails
  GROUP BY Color
  ORDER BY QuantitySold DESC

  END
  

  I see many similarities between stored procedures and functions.  Functions can return values. 

  1.  But can stored procedures return values?
  2.  In the example above, the stored procedure displays the results of a SELECT statement.  Do these records displayed by the SELECT statement count as a returned value?

  Stored procedures can return values. The results of the select statement you have would be the result set.
  Stored procedures have return values, output parameters and result sets. What you are referring to with returning values like functions would be output parameters.
  The return value for stored procedures is always there meaning you don't have to specify anything. It's usually used for success or failure. Sometimes people will use it similar to output parameters but I never do that. It's good to know if the stored procedure succeeded or not, especially with nested stored procedures. Some client code depends on having the 0 returned from a stored procedure for success. This documentation covers all three - return value, output parameters and result sets: 
  Return Data from a Stored Procedure

  Sue

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 29, 2019 at 5:16 pm

  #2020492

  Yes, and you can insert those values into a properly defined table (permanent, temporary, or variable).

  
create table #Sample(Color varchar(32), QuantitySold int);

  insert into #Sample(Color, QuantitySold)
exec ColorPopularity;


• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 29, 2019 at 10:40 pm

  #2020520

  Sue_H - Tuesday, January 29, 2019 5:15 PM

  michael.leach2015 - Tuesday, January 29, 2019 4:09 PM

  Sample code:

  
CREATE PROCEDURE ColorPopularity


  AS
  

  BEGIN

  SELECT Color, SUM(Quantity) AS QuantitySold
  FROM LineItemDetails
  GROUP BY Color
  ORDER BY QuantitySold DESC

  END
  

  I see many similarities between stored procedures and functions.  Functions can return values. 

  1.  But can stored procedures return values?
  2.  In the example above, the stored procedure displays the results of a SELECT statement.  Do these records displayed by the SELECT statement count as a returned value?

  Stored procedures can return values. The results of the select statement you have would be the result set.
  Stored procedures have return values, output parameters and result sets. What you are referring to with returning values like functions would be output parameters.
  The return value for stored procedures is always there meaning you don't have to specify anything. It's usually used for success or failure. Sometimes people will use it similar to output parameters but I never do that. It's good to know if the stored procedure succeeded or not, especially with nested stored procedures. Some client code depends on having the 0 returned from a stored procedure for success. This documentation covers all three - return value, output parameters and result sets: 
  Return Data from a Stored Procedure

  Sue

  I read the article.  I have a question about return codes in the screen below.

  

  The stored procedures returns a result set which is assigned to the variable @return_value.  Then it looks like the SELECT statement is displaying a column titled "Return Value" then it displays what is stored in @return_value.  The variable @return_value holds a data set from the SELECT statement.  So when that variable is displayed, why does it display 0 (I know this is a return code) instead of displaying the results of the SELECT statement?  I know that @return_value is declared as an INT, but even still shouldn't it attempt to display the records from the SELECT statement even with an error?

  My questions:

  1.  How does SQL know to display 0 as a return value after a stored procedure executes successfully?  I don't have that specified in my code for the stored procedure.
  2.  Since the result set is the result of the stored procedure, why doesn't that result set get assigned to @return_value (instead of 0)?

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  January 29, 2019 at 10:51 pm

  #2020522

  michael.leach2015 - Tuesday, January 29, 2019 10:40 PM

  Sue_H - Tuesday, January 29, 2019 5:15 PM

  michael.leach2015 - Tuesday, January 29, 2019 4:09 PM

  Sample code:

  
CREATE PROCEDURE ColorPopularity


  AS
  

  BEGIN

  SELECT Color, SUM(Quantity) AS QuantitySold
  FROM LineItemDetails
  GROUP BY Color
  ORDER BY QuantitySold DESC

  END
  

  I see many similarities between stored procedures and functions.  Functions can return values. 

  1.  But can stored procedures return values?
  2.  In the example above, the stored procedure displays the results of a SELECT statement.  Do these records displayed by the SELECT statement count as a returned value?

  Stored procedures can return values. The results of the select statement you have would be the result set.
  Stored procedures have return values, output parameters and result sets. What you are referring to with returning values like functions would be output parameters.
  The return value for stored procedures is always there meaning you don't have to specify anything. It's usually used for success or failure. Sometimes people will use it similar to output parameters but I never do that. It's good to know if the stored procedure succeeded or not, especially with nested stored procedures. Some client code depends on having the 0 returned from a stored procedure for success. This documentation covers all three - return value, output parameters and result sets: 
  Return Data from a Stored Procedure

  Sue

  I read the article.  I have a question about return codes in the screen below.

  

  The stored procedures returns a result set which is assigned to the variable @return_value.  Then it looks like the SELECT statement is displaying a column titled "Return Value" then it displays what is stored in @return_value.  The variable @return_value holds a data set from the SELECT statement.  So when that variable is displayed, why does it display 0 (I know this is a return code) instead of displaying the results of the SELECT statement?  I know that @return_value is declared as an INT, but even still shouldn't it attempt to display the records from the SELECT statement even with an error?

  My questions:

  1.  How does SQL know to display 0 as a return value after a stored procedure executes successfully?  I don't have that specified in my code for the stored procedure.
  2.  Since the result set is the result of the stored procedure, why doesn't that result set get assigned to @return_value (instead of 0)?

  Simply because that's not the way it works.  The variables used with OUT/OUTPUT/RETURN are scalar only.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 30, 2019 at 7:40 am

  #2020558

  michael.leach2015 - Tuesday, January 29, 2019 10:40 PM

   The variable @return_value holds a data set from the SELECT statement.  So when that variable is displayed, why does it display 0 (I know this is a return code) instead of displaying the results of the SELECT statement?  I know that @return_value is declared as an INT, but even still shouldn't it attempt to display the records from the SELECT statement even with an error?

  The @return_value doesn't hold the data set. It has the return value from the execution of the stored procedure. It wouldn't display the records from the select statement - that's the result set, not the return value. It's an integer and result sets can be more than just an integer. You may want to read that document again about return values, output parameters and result sets.

  Sue

• Chris Wooding

  SSCarpal Tunnel

  Points: 4530

  More actions

  January 31, 2019 at 9:30 am

  #2020726

  I tend to structure stored procedures like this.
  
begin try
  -- Do some stuff
  return 0;
end try

  begin catch
  -- Do some error handling
  return -1;
end catch

  That way whatever calls the procedure gets a clear indication of whether or not it worked. Depending on what the stored procedure does, it may also generate a result set and/or set the value of output parameters (it would do this prior to the return 0 line).

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 31, 2019 at 10:34 am

  #2020741

  Sue_H - Wednesday, January 30, 2019 7:40 AM

  michael.leach2015 - Tuesday, January 29, 2019 10:40 PM

   The variable @return_value holds a data set from the SELECT statement.  So when that variable is displayed, why does it display 0 (I know this is a return code) instead of displaying the results of the SELECT statement?  I know that @return_value is declared as an INT, but even still shouldn't it attempt to display the records from the SELECT statement even with an error?

  The @return_value doesn't hold the data set. It has the return value from the execution of the stored procedure. It wouldn't display the records from the select statement - that's the result set, not the return value. It's an integer and result sets can be more than just an integer. You may want to read that document again about return values, output parameters and result sets.

  Sue

  Ok.  After giving this some more thought, I think I've got it now.  Thank you.

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  January 31, 2019 at 10:43 am

  #2020742

  Chris Wooding - Thursday, January 31, 2019 9:30 AM

  I tend to structure stored procedures like this.
  
  begin try
    -- Do some stuff
    return 0;
  end try

  begin catch
  -- Do some error handling
  return -1;
end catch

  That way whatever calls the procedure gets a clear indication of whether or not it worked. Depending on what the stored procedure does, it may also generate a result set and/or set the value of output parameters (it would do this prior to the return 0 line).

  This is helpful.  Suppose you didn't use begin/end catch and you only used the first block of code.  If it works, it works.  But if it didn't work, I would think that whatever called the procedure would still know that it didn't work because an SQL would generate an error message right?

• Chris Wooding

  SSCarpal Tunnel

  Points: 4530

  More actions

  February 1, 2019 at 1:37 am

  #2020819

  michael.leach2015 - Thursday, January 31, 2019 10:43 AM

  Chris Wooding - Thursday, January 31, 2019 9:30 AM

  I tend to structure stored procedures like this.
  
  begin try
    -- Do some stuff
    return 0;
  end try

  begin catch
  -- Do some error handling
  return -1;
end catch

  That way whatever calls the procedure gets a clear indication of whether or not it worked. Depending on what the stored procedure does, it may also generate a result set and/or set the value of output parameters (it would do this prior to the return 0 line).

  This is helpful.  Suppose you didn't use begin/end catch and you only used the first block of code.  If it works, it works.  But if it didn't work, I would think that whatever called the procedure would still know that it didn't work because an SQL would generate an error message right?

  You'd have to leave out the begin try/end try as well and just have the enclosed code block. There must be a catch block if there's a try block. You're correct, you'd receive an error number and message even with no try/catch. I like using it because you can add information, eg. where in the procedure the error occurred (extremely useful if there are many steps).