September 25, 2009 at 6:13 pm
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.
September 25, 2009 at 7:06 pm
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
...and your only reply is slàinte mhath
September 25, 2009 at 7:26 pm
What do you mean by "data access interface inconsistent"? Why can't this be done? I mean it's just two conditions.
September 25, 2009 at 7:35 pm
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
...and your only reply is slàinte mhath
September 28, 2009 at 7:25 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2009 at 1:11 pm
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)
September 28, 2009 at 3:01 pm
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...
September 28, 2009 at 3:09 pm
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 3:09 pm
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2009 at 9:25 am
GSquared (9/28/2009)
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.
to correct the statement i think 'and a=0' should be inserted, but it is not much simpler then not '(a=0 and b=0)'
if (a + b) <> 0 and a = 0
begin
select * from table1 where id=@id
end
September 29, 2009 at 10:44 am
321 MySQL (9/29/2009)
GSquared (9/28/2009)
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.
to correct the statement i think 'and a=0' should be inserted, but it is not much simpler then not '(a=0 and b=0)'
if (a + b) <> 0 and a = 0
begin
select * from table1 where id=@id
end
Those solutions do not take into account the result when a is null or b is null.
This should work OK:
if case when a=0 and b=0 then 1 else 0 end = 0
begin
select * from table1 where id=@id
end
September 30, 2009 at 7:06 am
Yes, there are a lot of ways to accomplish this. I still think the simplest is to use "not" for the test.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2009 at 10:37 pm
Mark-459099 (9/25/2009)
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 executefor 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.
I think everyone has made it all too complicated with IF's, Cases, etc, etc. This is a simple problem with a simple solution....
SELECT * FROM Table1 WHERE ID = @ID AND @a> 0 AND @b-2>0
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2009 at 7:36 am
Jeff Moden (9/30/2009)
Mark-459099 (9/25/2009)
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 executefor 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.
I think everyone has made it all too complicated with IF's, Cases, etc, etc. This is a simple problem with a simple solution....
Part of the requirement was no result-set if a and b are not both 0. This would give an empty result-set. Also, to comply with the original requirement, it would need to return results if only one of them is 0 but the other isn't, while this wouldn't do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply