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?

Answers


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

Need Your Help

Missing libtdsodbc.so in freetds-dev - MSSQL on Ubuntu

sql-server tsql ubuntu odbc freetds

I'm trying to get MSSQL working on Ubuntu 12.04 via ODBC, and I've followed these steps to the letter:

How can I get the indices of children in a HTML ul sortable list, using jQuery/javascript?

javascript jquery html sortable html-lists

I have a list that is dynamically populated, I want to get the indices of children of that list.