• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Can You Nest Where Clauses in IF statements only with only one SELECT target list based on input parameter checks? - MAX points for quick working solution!

Hello Experts!

Can anyone tell me if it is possible to nest WHERE Statements inside IF statement to only run the Where caluse based on the input parameters a user may or may not enter. Currently in my select target list I am working wth over 50 fields in the target list with about 12 different user input scenarios and trying to not have to repeat the code for the target list twelve time as the target list is always the same but the WHEE portion is the only place it changes after checking what the user put in.

Example Code with Three of the 12 Where scenarios are list below.... Thanks for any help

CREATE PROCEDURE  myproc
@expirestart varchar(10),
@cst_id varchar(50),
@invoiceopencloseflag varchar(5),
@mbtcode varchar(5)

AS

SELECT ID, name, address, phone ---and about 50 other fields following here
From customertable

IF (@invoiceopencloseflag ='BOTH' OR @invoiceopencloseflag ='9' )
AND @mbtcode IS NOT NULL
AND @expirestart IS NULL
AND @cst_id IS NULL

WHERE
(vw_viewname.ivd_delete_flag <> '1'
AND dbo.***.mbt_code IN (@mbtcode))

IF @invoiceopencloseflag ='0'
AND @mbtcode
IS NOT NULL
AND @expirestart IS NOT NULL
AND @cst_id IS NULL

WHERE
(vw_viewname.ivd_delete_flag <> '1'
AND ivd_close_flag ='0'
AND dbo.***.mbt_code IN (@mbtcode)
AND mbr_expire_date IN (@expirestart))

IF @invoiceopencloseflag ='1'  
AND @mbtcode IS NOT NULL
AND @expirestart IS NOT NULL
AND @cst_id IS NULL

WHERE
(vw_viewname.ivd_delete_flag <> '1'
AND ivd_close_flag ='1'
AND dbo.***.mbt_code IN (@mbtcode)
AND mbr_expire_date IN (@expirestart) )

Is this possible or am I forced to repeat the same target list over and over agin 12 times...trying to consolidate the code up as much as possible for that part---
0
Tone' Shelby
Asked:
Tone' Shelby
3 Solutions
 
SQL_SERVER_DBACommented:
I've never seen the WHERE statement nested within an IF, your gonna need some Begin and End
0
 
_agx_Commented:
You could do this by building a dynamic sql string in a variable and executing the string with sp_executesql.

As mentioned, use BEGIN and END blocks to properly enclose your IF statements.
0
 
_agx_Commented:
To clarify, you can't use multiple where clauses.  You would use the IF statements to conditionally build the single where clause.

> vw_viewname.ivd_delete_flag <> '1'
> AND ivd_close_flag ='1'
> AND dbo.***.mbt_code IN (@mbtcode)
> AND mbr_expire_date IN (@expirestart) )

1. Do all 12 of the conditions deals with those same 4 columns?  
2. Do you really need an IN (....) clause?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LowfatspreadCommented:
wont
this suffice?
CREATE PROCEDURE  myproc
@expirestart varchar(10),
@cst_id varchar(50),
@invoiceopencloseflag varchar(5),
@mbtcode varchar(5)
WITH RECOMPILE
AS

SELECT ID, name, address, phone ---and about 50 other fields following here
  From customertable
  Left Outer Join vw_viewName
    on ...
  Left Outer Join dbo.vw_client_gcsaa_mbrshp_renewal_tshelby  
    on ....
 Where vw_viewname.ivd_delete_flag <> '1'
   And (dbo.***.mbt_code =@mbtcode
        or @mbtcode is null)    
   And (mbr_expire_date = @expirestart
          or @expirestart is null
       )
...


if not then as stated above you'll need to consider dynamic sql  and constucting the sql string ...
0
 
_agx_Commented:
> wont  this suffice?

   Yes, that's what I was wondering too.  

   @tshel,

   If all of your 12 conditions deal with the same 4 columns in the where clause, then you should be
   able to use Lowfatspread's suggestion, or a variation of it.  

   If the columns aren't always the same, or you really need to use an IN ( @listOfValues ) clause,
   then you may need to use dynamic sql.





   



0
 
modus_operandiCommented:
One of the view names obfuscated by request.

modus_operandi
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now