IF STATEMENT IN WHERE CLAUSE

• dream coder

  Right there with Babe

  Points: 723

  More actions

  August 8, 2008 at 6:35 am

  #195628

  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.

• JuncTheMayl

  Hall of Fame

  Points: 3962

  More actions

  August 8, 2008 at 6:40 am

  #855088

  SELECT * FROM employee

  WHERE FIELD1 = CASE @employee

  WHEN 'Value1' THEN 'Something'

  WHEN 'Value2' THEN 'Something Else'

  ELSE 'Something more'

  END

• dream coder

  Right there with Babe

  Points: 723

  More actions

  August 8, 2008 at 6:54 am

  #855106

  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....

• A_A_R_T

  SSC Eights!

  Points: 900

  More actions

  August 8, 2008 at 7:07 am

  #855124

  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

• JuncTheMayl

  Hall of Fame

  Points: 3962

  More actions

  August 8, 2008 at 7:12 am

  #855130

  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

• dream coder

  Right there with Babe

  Points: 723

  More actions

  August 8, 2008 at 7:19 am

  #855141

  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 ......

• JuncTheMayl

  Hall of Fame

  Points: 3962

  More actions

  August 8, 2008 at 7:24 am

  #855146

  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.

• dream coder

  Right there with Babe

  Points: 723

  More actions

  August 8, 2008 at 7:33 am

  #855164

  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'

• JuncTheMayl

  Hall of Fame

  Points: 3962

  More actions

  August 8, 2008 at 7:35 am

  #855166

  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

• dream coder

  Right there with Babe

  Points: 723

  More actions

  August 8, 2008 at 7:50 am

  #855184

  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

• JuncTheMayl

  Hall of Fame

  Points: 3962

  More actions

  August 8, 2008 at 7:58 am

  #855189

  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.

• Lynn Pettis

  SSC Guru

  Points: 442470

  More actions

  August 8, 2008 at 8:10 am

  #855203

  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).

  😎

• Lynn Pettis

  SSC Guru

  Points: 442470

  More actions

  August 8, 2008 at 8:18 am

  #855214

  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

  😎