3

This is a meta-question. How does one, using the INFORMATION_SCHEMA provided with each database, discover the return value(s) of a stored procedure? With functions, the return value is explicitly declared and shows up in the INFORMATION_SCHEMA under ROUTINES.

However, stored procedures seem to be a bizarre grey area (as always is the distinction between SP's and functions). They seem to officially have no 'return value', but yet at the end you can run a SELECT statement such as:

SELECT RowID = @RowID;

For example; and in C# you read the return value from the column 'RowID'. This makes it clear that stored procedures actually are capable of returning values, albeit not within the context of T-SQL code like functions do.

How does one discover these values and their types without having to parse the definition itself?

EDIT

If you are searching for this, the return values of stored procedures are integers implicitly, but also can return data called result sets.

  • http://msdn.microsoft.com/en-us/library/ff878236.aspx – ta.speot.is Mar 08 '13 at 22:26
  • The return values of stored procedures are called ... return values. They always return an integer (implicitly 0). But they can also have `OUT` parameters and result sets. – ta.speot.is Mar 08 '13 at 22:37
  • so when I SELECT ROWID = @RowID this is not a return value, but a result set? –  Mar 08 '13 at 22:40
  • It is the most technically correct way of describing it, I believe, but if a colleague came up to me and said "I expected the return value of this stored procedure to have 5 rows instead of 6" I'd know what he was talking about. – ta.speot.is Mar 08 '13 at 22:46
  • You complain about semantics. If you want to get this stuff right, semantics are crucial. Please don't brush them off. A result set and a return value are very different things! – Aaron Bertrand Mar 08 '13 at 22:49
  • The complaint is that the semantics are, in this case, confusing to anyone who is not an experienced at SQL SERVER / ORACLE. To any other programmer, the FUNCTION has a RETURN, PARAMETERS, and SIDE EFFECTS. In the case of stored procedures then, C# treats their SIDE EFFECTS as a RETURN, which while valid based on what actually happens, is misleading and in my opinion, reveals a flaw in the division between SP's and Functions. –  Mar 08 '13 at 22:56
  • I don't think there's much to be gained from discussing this further, but [C# treats a stored procedure's return value as its return value](http://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp) (note the `ParameterDirection.ReturnValue`). For what it's worth, within a stored procedure, your `SELECT RowID = @RowID` to return a result seems to be less idiomatic than `RETURN @RowID` (assuming `@RowID` is an integer). – ta.speot.is Mar 09 '13 at 00:53

1 Answers1

7

Stored procedures return integers.

If you want the result sets they return you can ask the SQL Server to parse the stored procedure to give you this information.

SQL Server 2012 introduces sys.dm_exec_describe_first_result_set_for_object which replaces SET FMTONLY.

CREATE PROC TestProc2
AS
SELECT object_id, name FROM sys.objects ;
SELECT name, schema_id, create_date FROM sys.objects ;
GO

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc2'), 0) ;
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc2'), 1) ;
GO

You can also investigate the parameters of a stored procedure, to see if any of them are marked OUT. INFORMATION_SCHEMA.PARAMETERS gives you each parameter's mode.

Looking at all three in combination should give you a lot of what you want.

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
  • 1
    Is anything of this sort available in 2008 R2 or 2008? –  Mar 08 '13 at 22:30
  • 3
    SET FMTONLY ON, but read the documentation before you use it. It has some problems. – ta.speot.is Mar 08 '13 at 22:32
  • This is mostly for generating input and output objects for services. It's a mediated process so it doesn't have to work flawlessly, it's just nice to be able to not have to manually rewrite the return type. –  Mar 08 '13 at 22:37