change the predicate to use "CASE"
-
how to change SQL statement below to use CASE instant of "AND" "OR" after condition where :- ALTER PROCEDURE dbo.authorInfor @au_lname varchar(40) AS select title, price, Name = substring (au_lname, 1,40), type from authors a, titles t, titleauthor ta where a.au_id = ta.au_id AND t.title_id = ta.title_id AND(au_lname = @au_lname OR au_lname = '' OR au_lname is Null) Thanks. Angela
-
how to change SQL statement below to use CASE instant of "AND" "OR" after condition where :- ALTER PROCEDURE dbo.authorInfor @au_lname varchar(40) AS select title, price, Name = substring (au_lname, 1,40), type from authors a, titles t, titleauthor ta where a.au_id = ta.au_id AND t.title_id = ta.title_id AND(au_lname = @au_lname OR au_lname = '' OR au_lname is Null) Thanks. Angela
If AND OR works use it; its must more efficient than using case. Look where you want to go not where you don't want to crash. Bikers Bible
-
how to change SQL statement below to use CASE instant of "AND" "OR" after condition where :- ALTER PROCEDURE dbo.authorInfor @au_lname varchar(40) AS select title, price, Name = substring (au_lname, 1,40), type from authors a, titles t, titleauthor ta where a.au_id = ta.au_id AND t.title_id = ta.title_id AND(au_lname = @au_lname OR au_lname = '' OR au_lname is Null) Thanks. Angela
CASEis generally used in instances where you want a different field value returned based on specified conditions. Your select statement is only concerned with returning all fields based on certain conditions. In other words,CASEwouldn't be used in theWHEREclause of an sql statement such as yours. You could clean this up a little though, like this:select title, price, Name = substring (au_lname, 1,40), type from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id where au_lname = @au_lname OR isnull(au_lname,'') = ''Or your where clause could be like this:where isnull(au_lname,'') IN (@au_lname,'')Other than that, nothing comes to my mind. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters -
If AND OR works use it; its must more efficient than using case. Look where you want to go not where you don't want to crash. Bikers Bible
-
CASEis generally used in instances where you want a different field value returned based on specified conditions. Your select statement is only concerned with returning all fields based on certain conditions. In other words,CASEwouldn't be used in theWHEREclause of an sql statement such as yours. You could clean this up a little though, like this:select title, price, Name = substring (au_lname, 1,40), type from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id where au_lname = @au_lname OR isnull(au_lname,'') = ''Or your where clause could be like this:where isnull(au_lname,'') IN (@au_lname,'')Other than that, nothing comes to my mind. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Petersno wonder i can't get what i want if i change the predicate to use "CASE" in my SQL statement :| can see that your SQL statement is more simplify and better than mine ;) but 1 of my condition is when user do not insert any value for author's name, all author's name record will display in my result.. if using your SQL statement above, if i insert null for my author's name, then the result is empty..
-
CASEis generally used in instances where you want a different field value returned based on specified conditions. Your select statement is only concerned with returning all fields based on certain conditions. In other words,CASEwouldn't be used in theWHEREclause of an sql statement such as yours. You could clean this up a little though, like this:select title, price, Name = substring (au_lname, 1,40), type from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id where au_lname = @au_lname OR isnull(au_lname,'') = ''Or your where clause could be like this:where isnull(au_lname,'') IN (@au_lname,'')Other than that, nothing comes to my mind. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. PetersI get a SQL statement that can use CASE under my condition
Declare @au_lname char(30) Select @au_lname = '' select title, price, Name = substring (au_lname, 1,40), type from titleauthor ta JOIN authors a on ta.au_id = a.au_id JOIN titles t on ta.title_id = t.title_id Where 1 = Case When RTRIM(@au_lname) = '' Then 1 When RTRIM(@au_lname) <> '' And RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname)) Then 1 Else 0 Endit's cool ;) angela -
I get a SQL statement that can use CASE under my condition
Declare @au_lname char(30) Select @au_lname = '' select title, price, Name = substring (au_lname, 1,40), type from titleauthor ta JOIN authors a on ta.au_id = a.au_id JOIN titles t on ta.title_id = t.title_id Where 1 = Case When RTRIM(@au_lname) = '' Then 1 When RTRIM(@au_lname) <> '' And RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname)) Then 1 Else 0 Endit's cool ;) angelaI see. Thank you for posting your solution. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters