我正在创建一个存储过程来返回搜索结果,其中一些参数是可选的.
我想要一个“if语句”在我的where子句,但不能让它工作. where子句只能使用非空参数进行过滤.
这是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
任何想法如何做到这一点？
解决方法
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))
