| Author |
Topic |
|
besadmin
Posting Yak Master
116 Posts |
 Posted - 2008-09-25 : 15:46:08
|
| Hey guys!I have a question. We have a view that returns an order status: Hold, Pending, or Transmit. I am going to display this on a web pages so you have a dropdown menu. the choised will be )HS, Null) or (P, T)So all i am really asking about is the query or view itself. I do i make a variable to say if HS get this, Else get P & T ?I hope i am presenting my need clearly. Any response is greatly appreciated. Thanks a lot friends! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-25 : 15:48:04
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110074 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-25 : 16:17:31
|
Oh,well. I think what besadmin is trying to do is replace the null values being returned in the database.ie if HS Value is null, replace it with P & T values.Instead of getting null back, he/she could use isnull. Thats if I am understanding correctly  So am pointing out a simple example in the previous post |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-25 : 16:40:04
|
quote:
Originally posted by besadmin
Hey guys!I have a question. We have a view that returns an order status: Hold, Pending, or Transmit. I am going to display this on a web pages so you have a dropdown menu. the choised will be )HS, Null) or (P, T)So all i am really asking about is the query or view itself. I do i make a variable to say if HS get this, Else get P & T ?I hope i am presenting my need clearly. Any response is greatly appreciated. Thanks a lot friends!
Well am hoping am understanding Besadmin. Who says he/she wants to display on his/her front end (drop down menu)quote:
he choised will be )HS, Null)or (P, T)
|
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-09-25 : 16:51:37
|
| O.K. Sorry, Hard to Explain. Lets forget about the front end pretty much.i have a view right now..it return things like name, order number, Status, etc...I am only concerned with the Status column. There are 3 possible values: HS (hold status) P (pending) or T (Transmitted)What i want to do is use variables i believe. so it will be like this...If Var1 = HSThen return all records where 'Status' = HSElse if Var1 = T & PThen return all record where 'Status' = T and P(as you can see...i only want two choices, return record with HS or return records with P and T together)I think that seems a little more clear: then eventually those variables will be chosen on a web page by drop down list...we will worry about that later though.Thanks a ton for you help guys!Appreciation is Great! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-10-02 : 11:46:30
|
| Hey Guys, i almost have this, but i need help setting a variableso i have Declare @Status VarChar(10)Set @Status 'HS' --This is one a user can chooseSet @Status 'P' 'T' --This is the one i need help with. Do not worry about the HS, that one works, but how do i set a variable to use AND is badically what i want to do. There are 3 possible values in the column HS, p, or t. So how do i set the variable to where it will select rows with the values p and with the values t??Thanks for any responses! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-03 : 01:45:45
|
quote:
Originally posted by besadmin
Hey Guys, i almost have this, but i need help setting a variableso i have Declare @Status VarChar(10)Set @Status 'HS' --This is one a user can chooseSet @Status 'P' 'T' --This is the one i need help with. Do not worry about the HS, that one works, but how do i set a variable to use AND is badically what i want to do. There are 3 possible values in the column HS, p, or t. So how do i set the variable to where it will select rows with the values p and with the values t??Thanks for any responses!
just set Status as varchar parameter and pass comma seperated values to it.then use code belowCREATE PROC YourProc@Status varhar(8000)=NULLASSELECT *FROM YourTableWHERE other conditions...AND (','+@Status+',' LIKE '%,'+ Status +',%'OR @Status IS NULL)then try passing valuesEXEC YourProc 'HS'EXEC YourProc 'P,T'EXEC YourProc... |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-03 : 08:56:50
|
| Oh come on. Don't be advocating dynamic SQL. He almost had itIf @Var1 = 'HS' select * from table where where Status = HSElse if @Var1 = 'T & P' or (@var='T' or @var='P') -- or whatever you need eslect * from table where Status in('T','P') -- or whatever not sure why Tara's solution wont work.Don't forget that status cannot equal 'T' and 'P', only 'T' OR 'P' - it can't be both |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-03 : 09:48:20
|
quote:
Originally posted by LoztInSpace
Oh come on. Don't be advocating dynamic SQL. He almost had itIf @Var1 = 'HS' select * from table where where Status = HSElse if @Var1 = 'T & P' or (@var='T' or @var='P') -- or whatever you need eslect * from table where Status in('T','P') -- or whatever not sure why Tara's solution wont work.Don't forget that status cannot equal 'T' and 'P', only 'T' OR 'P' - it can't be both
where did i use dynamic sql? did you mean my solution? |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-10-03 : 11:54:39
|
| Hey, thanks for all of your responses, i pretty much got it using if, else.I appreciate everyones responses though!Thanks a ton SQL Master of life! |
|
|
|
|
Help! Using @Variables