Conditional where clause T-SQL

I have a stored procedure that returns data.

I need to change the where clause based on parameters passed in.

For example, the parameters are:

@Region NVARHCAR(15)
@CountryCode NVARCHAR(2)
@ProductA BIT
@ProductB BIT
@Pro开发者_JAVA技巧ductC BIT

If @Region is passed in, then the where should select by region, if @CountryCode is passed in then the where should select by country code.

For the products, if any of them are set to true, the where should select the data for that project.

So the statement could look like this if @Region is passed in and @ProductA and @ProductC are set to true:

SELECT *
FROM table
WHERE Region = @Region AND
(Product = 'ProductA' OR Product = 'ProductC')

Alternatively, the product conditional could be an IN statement.

If @CountryCode was passed in it would look as follows:

SELECT *
FROM table
WHERE CountryCode = @CountryCode AND
(Product = 'ProductA' OR Product = 'ProductC')

It's even possible that @CountryCode and @Region could be passed in.

Is there any way to do this with T-SQL and not dynamic SQL generated from the app?

Thanks

You don't need to build a dynamic SQL statement, you just need to check the values of your parameters. Here is how I commonly build SQL clauses to achieve this:

WHERE ((@Region IS NULL) OR (Region = @Region))
AND ((@CountryCode IS NULL) OR (CountryCode = @CountryCode))
AND ((@ProductA = 0) OR (Product = 'ProductA'))
AND ((@ProductB = 0) OR (Product = 'ProductB'))
AND ((@ProductC = 0) OR (Product = 'ProductC'))

If your SQL is built like this, then you are only filtering on the Region column when you pass in a value for the @Region parameter. The same is true for CountryCode.

You can always build the SQL statement as a string using your conditions.. then simply execute the resulting statement string using sp_executesql (a command which basically executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically)...

I understand you may not want build sql strings but it a solution.

This isn't necessarily the cleanest approach, but would avoid anything dynamic:

SELECT *
FROM table
WHERE CountryCode = isnull(@CountryCode, CountryCode) AND
Region = isnull(@Region, Region) AND
(Product = 'ProductA' OR Product = 'ProductC')

I'd simplify and write a stored procedure for each case. Or at least add procedural logic:

IF NOT @Region IS NULL ....

and have separate queries that can optimize on their own merits.

EDIT:

A couple principles that I think apply:

http://en.wikipedia.org/wiki/Coupling_%28computer_programming

http://en.wikipedia.org/wiki/Single_responsibility_principle

If anything, the most direct approach (not necessarily the most elegant) is to set a default value for each parameter that is not a valid parameter value and perform a conditional check on each of them to see if the value contained in each parameter is different from the default value. Here I am assuming that the null value will never passed in as a valid value.

CREATE PROC sp_ProdInfo (
 @Region NVARHCAR(15) = NULL,
 @CountryCode NVARCHAR(2) = NULL,
 @ProductA BIT,
 @ProductB BIT,
 @ProductC BIT
) 
AS
 BEGIN
 -- other statements
 IF NOT @Region IS NULL
  BEGIN
   SELECT *
    FROM table
    WHERE Region = @Region AND
    (Product = 'ProductA' OR Product = 'ProductC')
  END
 ELSE
  BEGIN
   IF NOT @Country IS NULL
   BEGIN
    SELECT *
     FROM table
     WHERE CountryCode = @CountryCode AND
     (Product = 'ProductA' OR Product = 'ProductC')
   END
   ELSE
   BEGIN
    PRINT 'Neither Country nor Region was passed in.'
   END -- end inner if
  END -- end outer if
 -- other statements
 END

I would use a Common table Expression