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 |
|
gdeconto
Posting Yak Master
107 Posts |
 Posted - 2001-12-07 : 16:45:05
|
| I've got alot of sprocs that look like this:CREATE PROCEDURE sprocname @myint int, ...ASIF myint = 0 SELECT ... FROM ... ORDER BY ...ELSE SELECT ... FROM ... WHERE myint = @myint ORDER BY ...because many of the web pages I am doing require that ALL entries be returned or just the entry I specified by the @myint parameter (ie to list all entries that can be edited or the one to be edited).Am trying to figure out a way, WITHOUT creating a dynamic sql string to execute, that will consolidate the two selects into a single statement (so that I don't have to do an IF and don't need to repeat most of the SELECT stmt).Any ideas on a more elegant solution, guys?? |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2001-12-07 : 16:53:43
|
| This works. I would do some checks to make sure it does not hurt performance.CREATE PROCEDURE sprocname @myint int, ...ASSELECT ... FROM ... WHERE myint = Dbo.IsZero(@Myint,Myint) ORDER BY ...and Dbo.IsZero a function which will replace Input1 with Input2 if Input 1 is zero.Or Pass NULL in those cases and use COALESCE or ISNULL. CREATE PROCEDURE sprocname @myint int = NULL, ...ASSELECT ... FROM ... WHERE myint = ISNULL(@Myint,Myint) ORDER BY ...Edited by - toddv on 12/07/2001 16:54:33 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-08 : 06:52:18
|
| SELECT ... FROM ... WHERE (myint = @myint or @myint = 0)ORDER BY ...==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
d_ba_
Starting Member
1 Post |
Posted - 2001-12-09 : 11:50:19
|
| To gdeconto:In any case, your "non elegant" solution (with "if") has better perfomance.Good luck. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-09 : 12:05:58
|
| If myint is indexed and has good selectivity. |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2001-12-10 : 18:39:50
|
| why is it that after you ask what appears to be a very complicated question (one that you haven't been able to figure out), someone leaves you a super simple solution that makes you smack your forehead and say "oh, for cryin out loud! why didn't I think of that"..thanks to NR for the answer I was looking for (and should have figured out myself)thanks to ya'll for the other comments.dang, but my forehead is getting sore!!! |
|
|
|
|
|
|
|
|
need elegant non-dynamic dynamic WHERE