• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

USING MULTIPLE STORED PROCEDURE RESULT SETS IN ASP.NET

I have a stored procedure that produces a resultset from a query. If the resultset is empty it tries a second query. This results in two result sets, the first being empty. In ASP.NET the data reader sees no data, even though the second result set is populated.

Please help me either, to only return one result set (cancelling the empty one) or how to code ASP.NET (Visual Basic) to read the second result set.
0
BOIT
Asked:
BOIT
  • 2
  • 2
1 Solution
 
natlozCommented:
Can you do:

If(select count(*) your QUERY = 0)
begin
   second query
end
else
begin
  first query
end
0
 
BOITAuthor Commented:
Natloz, My query is contained within a variable and I am running it using a nested EXEC statement.

I tried using your suggestion already but got into difficulties with the syntax:
IF (EXEC('SELECT COUNT(*) FROM ('+@SQLTEXT+') A')=0)
gives
Error 156: Incorrect syntax near the keyword 'EXEC',
Line 81: Incorrect syntax near '='


0
 
natlozCommented:
Can you not specify an index if more then 1 table was returned? dataset.tables(0)
0
 
HavaganCommented:
You can move through the resultsets returned by your sproc using the SqlDataReader.NextResult() method and process the records in the resultset using the While SqlDataReader.Read() syntax.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult(VS.71).aspx
0
 
BOITAuthor Commented:
Thank you both for your suggestions. There may have been a way of manipulating the data within the stored procedure as suggested by Natloz, but I found moving to the second result set as suggested by Havagan to be simple and effective.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now