Basic SProc Question? If Recorset Empty

[gus121]

I have a stored proc with a basic query which is returned as a recordset.

However there are certain circumstances where the recordset may return no records. In which case i wish to execute anouther SQL db query instead. I wish to implement the 2nd query in the same stored proc how is this done?

My existing query is as follows.

ALTER    PROCEDURE [dbo].[usp_CategorySectionGet]
	@CategoryID int,
	@SiteID int
	
AS
SELECT
	[C].Section_ID,
	[C].Section_Mask,
	[S].SectionName
FROM
	tblCategories AS [C]
	
JOIN

tblSections AS [S]

ON
	[C].Section_ID = [S].SectionID

WHERE 
	[C].Category_ID = @CategoryID 
AND 
	([S].SiteMask & @SiteID > 0)

The SQL i would like to run if the recorset is empty is something like this:

SELECT
	[C].Section_ID,
	[C].Section_Mask,
	[S].SectionName
FROM
	tblCategories AS [C]
	
JOIN

tblSections AS [S]

ON
	[C].SectionMask_ID & [S].SectionMaskID

WHERE 
	[C].Category_ID = @CategoryID 
AND 
	([S].SiteMask & @SiteID > 0)

-Gus

 

[LFCfan]

You can check the value of @@ROWCOUNT right after you run the first query - if @@ROWCOUNT = 0, then no rows have been returned

[sub]"In vain have I struggled. It will not do."[/sub]

 

[gus121]

Hi thanks i tryed your method but it returned two recordsets and empty recordset and a second recordset.

I am running a little ASP app and i dont think it can cope easily with two recorsets.

thanks



-Gus

 

[LFCfan]

you could run a SELECT COUNT(...) before deciding which query to run?

[sub]"In vain have I struggled. It will not do."[/sub]

 

[gus121]

Hi thanks for your help.
I tryed using the If Exists Method and this seems to work.

For me neither your or my solution seems clean or an efficient way of running a query.

If anyone has a cleaner leaner solution then they would be most welcome to post it.

thanks

-Gus

 

[MisterMo]

if you are running more the one query in your SP then you should use

set nocount on

this will only return the final outcome of the SP

Mo

 

[gus121]

Hi Mo,

Since i put this query up i noticed that i had forgot to set nocount on. Which i normaly do as a matter of course as without setting no count on it normaly causes more problems than it is worth.

However the first query using the @@rowcount still apperes to return two recordsets and does not function in my ASP app even when Set NOCOUNT ON is set.

thanks

-Gus

 

[MisterMo]

The @@rowcount will always return the total rows of every query even with the nocount set.

I would declare a variable and do a select count as LFCfan suggested and then run the main query within an if statement.

Mo

 

[gus121]

When i mean it does not function i mean it throws up an error when i run the query in the ASP.

Surely this method is as good as the Count Method e.g:

If EXISTS(SELECT id FROM tblname WHERE id=1)
....
Else
....

Compared to:

SELECT @recordcount = count(id)FROM tblname WHERE id=1

If @recordcount > 0
.....
Else
....


thanks



-Gus

 

[MisterMo]

is not ASP that give you problems is ADO.
the trik is totry and fool ADO into believing that only one query is run

You could write a function to test for existing records
and then run your query

Mo

 

[gus121]

Hi Mo,

thanks i will bear that in mind. For the moment that is too much work.

thanks



-Gus