|
|
|
Valued Member
 
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59,
Visits: 138
|
|
How do you write a "do nothing" in sql. I have a statement where if two variables are 0, I don't want anything to execute
for example
if a=0 and b=0
'do nothing
else
select * from table1 where id=@id
do I put in "RETURN NULL" or is there a command I can use.
Any help would be appreciated.
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 748,
Visits: 1,515
|
|
Hi,
I think that you may need to think about the issue again. If you 'do nothing' in one case and return a rowset in the second, it means that your data access interface is inconsistent. You will have to handle fact of absence of a rowset in the higher layer.
If an empty rowset should be returned, just do select * from table1 where 1=0.
If you consider condition a=0 and b=0 as error, throw exception and handle it higher in the app.
Regards
Piotr
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59,
Visits: 138
|
|
| What do you mean by "data access interface inconsistent"? Why can't this be done? I mean it's just two conditions.
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 748,
Visits: 1,515
|
|
I mean, from the design point of view: you have the same set of parameters and the same set of return values unless an exception occurs. If you have a procedure that returns rowset or not, depending on some conditions, you can't for example open a reader and iterate through rows in the higher code - there may be no resultset (not empty resultset). And then you will have an exception.
In higher code, like in C# or Java or anything else, if you declare a function that returns int, it has to return int always, or throw exception. The same principle applies to stored procedures.
Regards
Piotr
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 7,383,
Visits: 15,141
|
|
You can simply put the statement RETURN in the IF statement like this:
IF (@a = 0) AND (@b = 0)
BEGIN
RETURN
END
ELSE
BEGIN
SELECT....
END
But, you'd be better off returning a value of some sort, say RETURN 1 or -1 so that the calling application knows that you're intentionally rejecting the query, not simply broken.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled
For better & quicker help read:
How to Post Performance Problems
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 74,
Visits: 38
|
|
what are your a and b variables doing? depending on what you're doing it could be answered in a set-based solution
select *
from table1
where (a=0 and b=0 and ID=-99999) --compare to value that id would never be, or some other logic you might want to filter on
or (ID>0)
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Monday, March 08, 2010 1:14 AM
Points: 69,
Visits: 142
|
|
Forgive me for being simple... LOL! Had to read the question a couple of times, but, isn't it easier just to do the following? Or did I miss the question by a mile?
if (a + b) <> 0
begin
select * from table1 where id=@id
end
If you don't want to do anything, then don't.  Don't know what the variables represent though... And, I agree, depending on how & where you want to execute this, you might want to add some type of error handling procedure/step...
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 8,617,
Visits: 4,916
|
|
Just on the T-SQL part of this:
if not (a=0 and b=0)
Then leave "else" out of it.
Of course, that will return positive if a equals 0 and b doesn't, or vice versa. Is that what you want?
On the design point, if a and b both equal 0, the proc won't return a result set, but if they don't, it will. That makes designing the rest of the software more complex, since sometimes it will have a result set to deal with, and sometime it won't. It's usually easier to program this kind of thing so that it has an empty result set under the conditions you want, rather than none at all.
If you're not sure what I mean by that, talk to whomever builds the software this query will be used by about it. They might be fine with it, but it's important to check that. If that's you, then I guess I'm telling you to talk to yourself, so you can ignore this part.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 8,617,
Visits: 4,916
|
|
Tobie-189314 (9/28/2009)
Forgive me for being simple... LOL! Had to read the question a couple of times, but, isn't it easier just to do the following? Or did I miss the question by a mile? if (a + b) <> 0 begin select * from table1 where id=@id end If you don't want to do anything, then don't. Don't know what the variables represent though... And, I agree, depending on how & where you want to execute this, you might want to add some type of error handling procedure/step...
What if a = -1 and b = 1? That'll foil this test.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 1,584,
Visits: 4,429
|
|
You could also have it do almost nothing.
declare @a int , @b int, @c int
select @a=0,@b=0
if @a=0 and @b=0
set @c=@c
else
select * from table1 where id=@id
|
|
|
|
