We help IT Professionals succeed at work.

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!

191 Views
Last Modified: 2010-03-19
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---
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015

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.
CERTIFIED EXPERT
Most Valuable Expert 2015

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?
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
One of the view names obfuscated by request.

modus_operandi
Community Support Moderator
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.