single recordset wanted - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

single recordset wanted

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-15 : 23:01:50

I've got a stored proc which returns a recordset. If by chance the recordset is empty, I'd like to return another recordset.

SELECT whatever

IF @@RECORDCOUNT = 0 BEGIN
SELECT someotherset
END

but I'd like "someotherset" to NOT be the 2nd recordset following an empty recordset. Is there an easy way to make this happen?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-15 : 23:09:33

Create a temp table or table var, insert the first set into it, if there are no rows, insert the second. Return the temp table.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-15 : 23:12:33

this might not be the best way to do this..

if exists(select whatever)
select whatever
else
select otherset

but this can be taken care of at the front end as you can check no of recordsets returned by the SP.

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-15 : 23:22:43

I ended up doing the if exists solution.

A recordset rewind would be handy.

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 05:46:57

To avoid pulling the query twice we have a client-side directive that can be given a hint to ignore an (immediately following) blank recordset. All our SQL Calls go through a central function which checks for the "special" recordset and acts on it accordingly.

So

SELECT [MyHint] = 'IGNORE_IF_EMPTY'

SELECT whatever

IF @@ROWCOUNT = 0
SELECT otherset

Our "fnExecuteSQL" function, client side, has a sniff of the first recordset, and if the first column name is [MyHint] then it does whatever the appropriate action is.

We also have a "fnGetNextRecordset" which "moves" to the next recordset and returns True/False as to whether another one exists. It too has a "sniff" of the recordset.

The sort of things we have available as [MyHint] include:

Setting global variables (so SQL can influence the behaviour of the application)

Forcing the Cached data to reload (partially or in full) - e.g. if a record has been "saved" which is known to be cached.

Forcing an Error at the client for critical error situation

Forcing a Redirect (web page application!)

Influencing Meta Tags (web page again!)

Requesting processing of an XML recordset

Outputting DEBUG data (e.g. displayed at the bottom of the page)

Having SQL return data to be EVAL'd by the client

We use something far more obtuse than [MyHint] for the column name!

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-16 : 08:20:54

In retrospect, Merkin's solution is probably best for large or high-cost queries. In my case, I just didn't want to code the same query twice...

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-16 : 08:39:02

Merkin's idea is the way to go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources