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.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.