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.
| Author |
Topic |
|
NickyW
Starting Member
10 Posts |
 Posted - 2003-04-01 : 06:30:49
|
| Hi,I have stored procedure which runs query A and then if the results are zero(i.e. @@rowcount=0) I want it to run query B. This works fine, the trouble is, the query returns two results sets one empty one from query A and the results of query B .Is there anyway I can 'clear all results sets that are going to be returned.' before running query B, so that I would only get one result set returned from the SP?I realise there are several work arounds to this problem( for example I could select into a variable, count the records and only return if there were some records etc.), but I am just interested in seeing if there is a command which 'clears all result sets that are going to be returned '.Thanks in advance,Nicky |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-04-01 : 07:04:17
|
| Nope, no such command ...You'll have to look one of your work arounds.Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-01 : 07:23:31
|
| Why not:CREATE PROCEDURE GimmeeSomething ASIF EXISTS(SELECT * FROM TableA WHERE SomeCol='SomeValue')SELECT * FROM TableA WHERE SomeCol='SomeValue'ELSESELECT * FROM TableB WHERE SomeOtherCol='SomeOtherValue'You'll always get only one result set from this. And the EXISTS test is not all that expensive, if it is, simply change it to a NOT EXISTS check and switch places of the two queries below. |
|
|
|
|
|
|
stop selects being returned from an SP