SQL if statement in where clause for searching database
I'm creating a stored procedure to return search results where some of the parameters are optional.
I want an "if statement" in my where clause but can't get it working. The where clause should filter by only the non-null parameters.
Here's the sp
ALTER PROCEDURE spVillaGet -- Add the parameters for the stored procedure here @accomodationFK int = null, @regionFK int = null, @arrivalDate datetime, @numberOfNights int, @sleeps int = null, @priceFloor money = null, @priceCeil money = null AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select tblVillas.*, tblWeeklyPrices.price from tblVillas INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK where If @accomodationFK <> null then accomodationTypeFK = @accomodationFK @regionFK <> null Then And regionFK = @regionFK IF @sleeps <> null Then And sleeps = @sleeps IF @priceFloor <> null Then And price >= @priceFloor And price <= @priceCeil END
Any ideas how to do this?
select tblVillas.*, tblWeeklyPrices.price from tblVillas INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK where (@accomodationFK IS null OR accomodationTypeFK = @accomodationFK) AND (@regionFK IS null or regionFK = @regionFK) AND (@sleeps IS null OR sleeps = @sleeps) AND (@priceFloor IS null OR (price BETWEEN @priceFloor And @priceCeil))
We've used a lot of COALESCE here in the past for "dynamic WHERE clauses" like you're talking about.
SELECT * FROM vehicles WHERE ([vin] LIKE COALESCE(@vin, [vin]) + '%' ESCAPE '\') AND ([year] LIKE COALESCE(@year, [year]) + '%' ESCAPE '\') AND ([make] LIKE COALESCE(@make, [make]) + '%' ESCAPE '\') AND ([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '\')
A big problem arises though when you want to optionally filter for a column that is also nullable... if the data in the column is null for a given row AND the user didn't enter anything to search by for that column (so the user input is also null), then that row won't even show up in the results (which, if your filters are optional, is incorrect exclusionary behavior).
In order to compensate for nullable fields, you end up having to do messier looking SQL like so:
SELECT * FROM vehicles WHERE (([vin] LIKE COALESCE(@vin, [vin]) + '%' ESCAPE '\') OR (@vin IS NULL AND [vin] IS NULL)) AND (([year] LIKE COALESCE(@year, [year]) + '%' ESCAPE '\') OR (@year IS NULL AND [year] IS NULL)) AND (([make] LIKE COALESCE(@make, [make]) + '%' ESCAPE '\') OR (@make IS NULL AND [make] IS NULL)) AND (([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '\') OR (@model IS NULL AND [model] IS NULL))
Just so you understand, IF is procedural code in T-SQl. It canot be used in an insert/update/delete/select statement it can only be used to determine which of two statements you want to run. When you need different possibilities within a statement, you can do as above or use a CASE statement.
You can also use IsNull or Coalesce function
Where accomodationTypeFK = IsNull(@accomodationFK, accomodationTypeFK) And regionFK = Coalesce(@regionFK,regionFK) And sleeps = IsNull(@sleeps,sleeps ) And price Between IsNull(@priceFloor, Price) And IsNull(priceCeil, Price)
This does the same thing as Michael's suggestion above...
IsNull(), and Coalesce() work more or less the same way, they return the first non-Null argument in the list, except iSNull only allows 2 arguments, and Coalesce can take any number...
Try putting your IF statement around the entire SQL statement. That means will have one SQL statement for each condition. That worked for me.