January 24, 2008 at 12:54 pm
I have a SP that takes 5 inputs from a pick list as input parameters @Color1, @Color2, @Color3, @Color4, @ Color5. How can I make sure that the user does not pick the same color in the input parameters? (For example, I want to prevent @Color1 = 'Red'; @Color4 = 'Red').
Apprecaite any response. Thanks inadvance.
sg2000
January 24, 2008 at 2:39 pm
Hmmm...either write code in the client that prevents this, or write code in the SP that ignores it. Perhaps the IF statement could be useful...
January 24, 2008 at 3:03 pm
Personally - I'd prevent this on the UI side (wherever they're picking the colors from). If that's a scenario you want to prevent - I wouldn't let it be submitted that way.
Cascading drop-downs would make it fairly easy to prevent (remove the previously selected colors from the list).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 24, 2008 at 3:03 pm
You could use a LOT of if statements to check for any equalities, or perhaps something like (the syntax ought to be correct)
if 5 = (
select
count(distinct colour)
from (
select @colour1 as colour
UNION ALL select @colour2
UNION ALL select @colour3
UNION ALL select @colour4
UNION ALL select @colour5
)
)
--all ok :)
begin
end
else
--not ok :(
begin
end
The nice thing is that if you have 6 colours then it's easy to step it up to 6. It's early in the morning here and I'm up even earlier due to being on nappy patrol so there may be a more elegant/faster/better way of doing it.
January 24, 2008 at 5:29 pm
If it is possible that you will not always have 5 colors (maybe they can pick 0, 1, or more colors?), then the following code may be a better solution.
if exists (
select
*
from(
select @color1 as colorUNION ALL
select @color2 UNION ALL
select @color3 UNION ALL
select @color4 UNION ALL
select @color5
) a
where
a.color is not null
group by
a.color
having
count(*) <> 1
)
begin
-- Do error processing for duplicate colors
end
January 24, 2008 at 8:23 pm
Go with Michael's solution 🙂
January 25, 2008 at 12:35 am
Thank you all for the valuable suggestions. I got it working using Michael's solution. It is perfect for what I need.
Yes, as Matt suggested, I can use cascading drop-downs up front to prevent this happening. However, I just double check the validity on the backend. May be this is overkill?
Well, the question is: should we bother to check whether an input parameter with value from a picklist is valid? Theoritically, it must be valid, right?
Thanks again.
sg2000
January 25, 2008 at 7:38 am
Well - depending on what the purpose is - no - it's not overkill. Actually - if it's really critical, then BOTH sides need to check (the UI side needs to check that it's not sending garbage, and the SQL side need to check that it's not GETTING garbage).
The validation is not at all overkill. The main reason I suggested the UI side is that process-wise you tend to be "stuck" when you're at the database side with junk as input: you can either default in something NOT based on user input, or - fail, which means sending the error back to the UI, and the UI STILL has to handle the issue and send the new inputs back to the DB.
Catching it up front gives you options which don't involve having the database server do your validation (especially on something that the UI can do easily all by itself).
That being said - the other proposed solutions are great for validating your inputs as they currently stand.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 25, 2008 at 7:50 am
Thanks Matt for the insight.
sg2000
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply