gpt4 book ai didi

sql-server - SQL if语句在where子句中用于搜索数据库

转载 作者:行者123 更新时间:2023-12-02 07:26:30 26 4
gpt4 key购买 nike

我正在创建一个存储过程来返回搜索结果,其中某些参数是可选的。

我想要在我的 where 子句中添加一个“if 语句”,但无法使其正常工作。 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))

关于sql-server - SQL if语句在where子句中用于搜索数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/317377/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com