Output Parameters from stored Proc to ASP webpage

[gus121]

Hi i have a stored proc which has three Sql queies in it.
2 of the queries i am outputing as output parameters. The third query i output as a recordset.

I am having trouble because i have circumstance where the result of one or more query brings back no result sets.

This appears to result in the failure to retrieve any data from the query to my ASP web application. Is this what i should expect?

Do i have to seperate these queries into seperate stored procs and run them indiviually?

I seem to be able to 'Exec' the stored proc ok but this does not display the results of the output parameters instead just displays the recordset how do i display the results of the output parameters?


thanks for your time


-Gus

-Gus

 

[Sheco]

This Microsoft KB article is not exactly on point but it discusses a related issue:

http://support.microsoft.com/kb/q245179/

 

[rac2]

Good article Sheco. Thanks.

The short answer for me when I had trouble returning both a recordset and output parameters was this. The recordset must be closed before the output parameters are available. So get the data from the recordset, close it, then get the data from the Parameters.

If that is awkward then use two stored procs, one for the recordset and one for the output parameters. Or use a SELECT in the stored proc to return a recordset with the values that you are trying to get via output parameters followed by a second SELECT for the rows of data; that way you can obtain the values first followed by the rows.

 

[Sheco]

Thanks for describing how you fixed the problem.

This will be a big help to every developer with the same problem that uses the search feature on the site... like me!

 

[Sheco]

I mean not that I have this problem right now, but I've had a similar problem before and I always gotta search for the answer 'cause the solution won't seem to stay stuck in my brain.