August 8, 2008 at 6:35 am
Dear All,
I was wondering what the syntax what be to append an IF statement in the where clause for example
SELECT * FROM employee
WHERE
IF @employee = 'Some value' THEN 'some condition'
IF @employee = 'some other value' THEN 'some other condition'
Is there a way of acheiving this kind of SQL statement?
Thanks in advance.
August 8, 2008 at 6:40 am
SELECT * FROM employee
WHERE FIELD1 = CASE @employee
WHEN 'Value1' THEN 'Something'
WHEN 'Value2' THEN 'Something Else'
ELSE 'Something more'
END
August 8, 2008 at 6:54 am
Thanks, but i need a IF because of the following:
DECLARE @product_name nvarchar(3)
SET @product_name = 'All'
SELECT * FROM product
WHERE product_name = CASE @product_name
WHEN 'All' THEN (SELECT 'a' UNION SELECT 'a' UNION SELECT 'c')
WHEN 'a' THEN 'a'
WHEN 'b' THEN 'b'
ELSE ''
END
this causes me an error because WHEN 'All' THEN (SELECT 'a' UNION SELECT 'a' UNION SELECT 'c')
returns more then one value and it crashes. So I was thinking of doing some kind of If logic to have seperate WHERE clauses appended....
August 8, 2008 at 7:07 am
Have you tried encapsulating your IF statements between "BEGIN...END"?
For example:
SELECT * FROM employee
BEGIN
IF @employee = 'Some value' THEN 'some condition'
END
BEGIN
IF @employee = 'some other value' THEN 'some other condition'
END
August 8, 2008 at 7:12 am
you'll probably have to do something like:
if @product_name = 'all'
select * from product where product_name in (select 'a' union select 'b'...)
else
select * from product where product_name = case @product_name
when 'a' then 'a'
when 'b' then 'b'
else 'c'
end
August 8, 2008 at 7:19 am
Hey, the IF statement casues the following error:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'THEN'.
Any ideas?
I would do two different statements but I will have to apply the same logic to other SQL statements within the my store, so i wont to limit the amount of SQL i write, hence I thinknig of using the IF statement ......
August 8, 2008 at 7:24 am
if... then is vb. the sql syntax is:
if condition
statement
else
statement
begin... end is only required when more than one statement is executed in a particular branch. for example:
if 1=1
begin
do something
do something else
end
else
do something again
or, from BOL:
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
Boolean_expression
Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.
{ sql_statement | statement_block }
Is any Transact-SQL statement or statement grouping as defined by using a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement.
To define a statement block, use the control-of-flow keywords BEGIN and END.
August 8, 2008 at 7:33 am
Oh i see so that was vb syntax
Is it possible for you to tell me how to implement the following logic then in the WHERE clause?
SELECT * FROM products
WHERE product_name IN
If @product_name <> 'All'
CASE WHEN product = 'a' then 'b'
CASE WHEN product = 'b' then 'c'
else ''
end
If @product_name = 'All'
SELECT 'a' union SELECT 'b' union select 'c'
August 8, 2008 at 7:35 am
see 3 posts above:
if @product_name = 'all'
select * from product where product_name in (select 'a' union select 'b'...)
else
select * from product where product_name = case @product_name
when 'a' then 'a'
when 'b' then 'b'
else 'c'
end
August 8, 2008 at 7:50 am
Thanks i saw that step, i would still like to know if u can use an "IF" in the WHERE clause of the SQL statement cause i dont want to write alot of SQL cause il need to be using this logic in the rest of the store proc, but in the mean time i will use the suggestion u suggested, thanks
August 8, 2008 at 7:58 am
the problem you're running into is that depending on a condition, you want to either select from a list of values or from one value. the easy way to fix that is to choose from a list of values each and every time:
select * from sometable where value in ('a','b','c')
select * from sometable where value in ('a')
select * from sometable where value in ('b')
select * from sometable where value in ('c')
so you'd do something like
select * from sometable where value in case condition when 1 then ('a','b','c') when 2 then ('a') else ('b') end
but that doesn't work because the case wants to return a literal. so the other option is to branch BEFORE you do your select. not the most elegant solution, but it should work. there may be a better way... i just can't think of it off the top of my head.
August 8, 2008 at 8:10 am
dream coder (8/8/2008)
Thanks i saw that step, i would still like to know if u can use an "IF" in the WHERE clause of the SQL statement cause i dont want to write alot of SQL cause il need to be using this logic in the rest of the store proc, but in the mean time i will use the suggestion u suggested, thanks
Answer, no. IF is a control flow statement used to control the logical flow of a script, stored procedure, or user-defined function. If you need to use IF in your stored procedure, use it control which SELECT statement you wish to execute based on the value passed in via the stored procedures parameter(s).
😎
August 8, 2008 at 8:18 am
dream coder (8/8/2008)
Thanks, but i need a IF because of the following:DECLARE @product_name nvarchar(3)
SET @product_name = 'All'
SELECT * FROM product
WHERE product_name = CASE @product_name
WHEN 'All' THEN (SELECT 'a' UNION SELECT 'a' UNION SELECT 'c')
WHEN 'a' THEN 'a'
WHEN 'b' THEN 'b'
ELSE ''
END
this causes me an error because WHEN 'All' THEN (SELECT 'a' UNION SELECT 'a' UNION SELECT 'c')
returns more then one value and it crashes. So I was thinking of doing some kind of If logic to have seperate WHERE clauses appended....
Based on this query, you either want to return all products in @product_name is 'All' or just the product name requested. The following query will meet that requirement.
DECLARE @product_name nvarchar(3)
SET @product_name = 'All'
SELECT
*
FROM
dbo.product
WHERE
(product_name = @product_name) -- return only this product
OR (@product_name = 'All'); -- OR return them all
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply