February 5, 2016 at 9:09 am
If I run EXEC spMyStoredProc I get two results:
387961
387962
February 5, 2016 at 9:14 am
If I run EXEC spMyStoredProc I get two results:
387961
387962
OK So Run
DECLARE @ResultSet INT
EXEC @Resultset =spMyStoredProc
SELECT @ResultSet
and tell me what you get
February 5, 2016 at 9:28 am
There are basically three ways to return data from a stored procedure.
1. (The version you are using in the stored procedure itself): just run a SELECT statement. The result will be returned in the form of a result set to the client. The client is not the same as the procedure calling the stored procedure - the client is SSMS when testing code in SSMS, and often is some .Net application in the final version of the code. That's where the result set will go. If a stored procedure calls a second stored procedure, it will not see any data returned from that second stored procedure in this way.
(** exception - if you call a stored procedure using INSERT ... EXECUTE, you will be able to capture these results)
2. (Recommended for scalar values): as an output parameter. Declare the parameter like you do a normal parameter, but add the OUTPUT property. Add the same OUTPUT property to the variable you pass in when calling the stored procedure. The procedure has to assign a value to that parameter, and that value will then also be assigned to the variable used when the stored procedure is called. Examples of this can be found in Books Online.
3. (The version you are using in the call of the stored procedure, used for a single numeric value and recommended to be used only for success or failure): as the return code of the stored procedure. This is set in the stored procedure by using the RETURN [Expression]; statement. (Leave out expression or let flow of controle reach the end of the procedure and 0 will be returned). The caller can grab the value by using Exec @variable = dbo.ProcName;
I hope this helps. Books Online has lots of examples on all of these.
February 5, 2016 at 9:48 am
OK I have run this:
DECLARE @ResultSet INT
EXEC @Resultset =spMyStoredProc
SELECT @ResultSet
I get two results
First result:
EXEC @Resultset =spMyStoredProc returns
387961
387962
Second result:
SELECT @ResultSet returns
0
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply