SQL Server Stored Procedure Return Value Problem

[p8ass8]

I have a stored procedure that returns the total number of records(INT) obtained for a particular parameter value(customer type).

I need to create a report that runs this stored procedure for several different parameter values and displays the total for each type.

The original SP does not have output parameters,and since this is an important SP, we would rather not mess with it.

Is it possible for me to grab the return value into a variable? That way, I can use the variables in my report.

Please help.
Thanks in advance.

 

[genomon]

What reporting tool are you planning on using? Also, I can't see how you could wreck the integrity of a sp by simply adding an output variable...

"Rome did not create a great empire by having meetings, they did it by killing all those who opposed them."

 

[gmmastros]

[tt]
Declare @Result Int

Exec @Result = [!]YourStoredProcedureHere[/!] [!]ParameterHere[/!]
[/tt]

 

[p8ass8]

genomon, I am using Crystal Reports 10. I am sure an output parameter won't wreck the SP, but a lot of processes seem to depend on this SP and we 'd rather not touch it and cause unanticipated problems elsewhere.

gmmastros, I tried this, the variable displays 1 - just that the procedure was executed successfully.

Thanks for your replies. If you have any other suggestions, please let me know. My last resort would be to modify the current SP.

 

[gmmastros]

>> gmmastros, I tried this, the variable displays 1 - just that the procedure was executed successfully.

I suggest you look again (unless I mis-understand you) .

It appears as though you are trying to capture the return value of a stored procedure, in which case, the method I showed should work just fine. I tested this before I posted. here's what I had...

First, the test stored procedure:

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] TestReturnValue
[COLOR=blue]As[/color]
[COLOR=blue]Return[/color] 7

Then, the code to test it.

[COLOR=blue]Declare[/color] @Result [COLOR=blue]int[/color]

[COLOR=blue]Exec[/color] @Result = TestReturnValue

[COLOR=blue]Select[/color] @Result

The output of the test code is....

[tt][blue]
-----------
7

(1 row(s) affected)
[/blue][/tt]

As I've demonstrated, the code works. Either I am misunderstanding you, or the problem with your code is occurring somewhere else.

 

[p8ass8]

gmmastros, I am sorry. I should have explained properly. Here is how the code looks right now:

If(parameter=1)
Begin
Select Count(*) from ...
Return (1)
End

When the procedure is executed, it comes back with the right number of records. But when I do,

Declare @NumRecs INT
exec @NumRecs=SP_name parameter_1

the '1' is returned(not sure who did it like this and why)

If I change the procedure to, say:

Declare @Result INT

If(parameter=1)
Begin
Select @Result=Count(*) from ...
Return (@Result)
End

Then I suppose what you are doing would work.

I will have to see if this will affect any other code based on the current structure of the procedure. Thanks.

 

[gmmastros]

So, your stored procedure returns a single value, but as a rowset?

You can still capture the output, but in a different way. Do a google search on [google]SQL Server Insert Exec[/google].

 

[p8ass8]

gmmastros, Thats what I need.

Thanks a lot!!