set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go--更新AccountID返回用户名称ALTER FUNCTION [dbo].[Fn_GetLendHouseCount]
(
@CommunityID INT=0,
@LArea INT=0,
@UArea INT=0,
@LLendPrice FLOAT=0,
@ULendPrice FLOAT=0,
@Rooms INT=0,
@HouseType INT=0,
@RefreshDays INT=0
)
RETURNS INT
AS
BEGIN
DECLARE @LendHouseCount INT SELECT @LendHouseCount=Count(*) FROM dbo.LendHouse
INNER JOIN Building ON LendHouse.CommunityID = Building.BuildingID
WHERE HouseState=1 and LendHouse.State=4 and AgentSourceID = 0
AND CommunityID=(CASE WHEN @CommunityID >0 THEN @CommunityID ELSE CommunityID END )
AND ISNULL(LendPrice,0)>=(CASE WHEN @ULendPrice>@LLendPrice THEN @LLendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendPrice,0)<=(CASE WHEN @ULendPrice>@LLendPrice THEN @ULendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendHouse.Area,0)>=(CASE WHEN @UArea>@LArea THEN @LArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(LendHouse.Area,0)<=(CASE WHEN @UArea>@LArea THEN @UArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(Rooms,0)=(CASE WHEN @Rooms>0 THEN @Rooms ELSE ISNULL(Rooms,0) END)
AND ISNULL(HouseType,0)=(CASE WHEN @HouseType>0 THEN @HouseType ELSE ISNULL(HouseType,0) END)
and DATEDIFF(day, LendHouse.publishtime, getdate()) <= @RefreshDays
RETURN @LendHouseCount
END请高人指点错误,谢谢了!
set QUOTED_IDENTIFIER ON
go--更新AccountID返回用户名称ALTER FUNCTION [dbo].[Fn_GetLendHouseCount]
(
@CommunityID INT=0,
@LArea INT=0,
@UArea INT=0,
@LLendPrice FLOAT=0,
@ULendPrice FLOAT=0,
@Rooms INT=0,
@HouseType INT=0,
@RefreshDays INT=0
)
RETURNS INT
AS
BEGIN
DECLARE @LendHouseCount INT SELECT @LendHouseCount=Count(*) FROM dbo.LendHouse
INNER JOIN Building ON LendHouse.CommunityID = Building.BuildingID
WHERE HouseState=1 and LendHouse.State=4 and AgentSourceID = 0
AND CommunityID=(CASE WHEN @CommunityID >0 THEN @CommunityID ELSE CommunityID END )
AND ISNULL(LendPrice,0)>=(CASE WHEN @ULendPrice>@LLendPrice THEN @LLendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendPrice,0)<=(CASE WHEN @ULendPrice>@LLendPrice THEN @ULendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendHouse.Area,0)>=(CASE WHEN @UArea>@LArea THEN @LArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(LendHouse.Area,0)<=(CASE WHEN @UArea>@LArea THEN @UArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(Rooms,0)=(CASE WHEN @Rooms>0 THEN @Rooms ELSE ISNULL(Rooms,0) END)
AND ISNULL(HouseType,0)=(CASE WHEN @HouseType>0 THEN @HouseType ELSE ISNULL(HouseType,0) END)
and DATEDIFF(day, LendHouse.publishtime, getdate()) <= @RefreshDays
RETURN @LendHouseCount
END请高人指点错误,谢谢了!
在函数内不正确地使用了 'getdate'。
(
@CommunityID INT=0,
@LArea INT=0,
@UArea INT=0,
@LLendPrice FLOAT=0,
@ULendPrice FLOAT=0,
@Rooms INT=0,
@HouseType INT=0,
@RefreshDays INT=0
)
RETURNS INT
AS
BEGIN
DECLARE @LendHouseCount INT SELECT @LendHouseCount=Count(*) FROM dbo.AAA
WHERE DATEDIFF(day, K_DATE, getdate()) <= @RefreshDays
RETURN @LendHouseCount
END
兄弟,俺的这样可以啊.你先在查询语句中执行下你的语句看是否可以
建个视图试试
create view v_getdate
as
Select Getdate() as gdate
go
--更新AccountID返回用户名称ALTER FUNCTION [dbo].[Fn_GetLendHouseCount]
(
@CommunityID INT=0,
@LArea INT=0,
@UArea INT=0,
@LLendPrice FLOAT=0,
@ULendPrice FLOAT=0,
@Rooms INT=0,
@HouseType INT=0,
@RefreshDays INT=0
)
RETURNS INT
AS
BEGIN
DECLARE @LendHouseCount INT SELECT @LendHouseCount=Count(*) FROM dbo.LendHouse
INNER JOIN Building ON LendHouse.CommunityID = Building.BuildingID
full join v_getdate
WHERE HouseState=1 and LendHouse.State=4 and AgentSourceID = 0
AND CommunityID=(CASE WHEN @CommunityID >0 THEN @CommunityID ELSE CommunityID END )
AND ISNULL(LendPrice,0)>=(CASE WHEN @ULendPrice>@LLendPrice THEN @LLendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendPrice,0)<=(CASE WHEN @ULendPrice>@LLendPrice THEN @ULendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendHouse.Area,0)>=(CASE WHEN @UArea>@LArea THEN @LArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(LendHouse.Area,0)<=(CASE WHEN @UArea>@LArea THEN @UArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(Rooms,0)=(CASE WHEN @Rooms>0 THEN @Rooms ELSE ISNULL(Rooms,0) END)
AND ISNULL(HouseType,0)=(CASE WHEN @HouseType>0 THEN @HouseType ELSE ISNULL(HouseType,0) END)
and DATEDIFF(day, LendHouse.publishtime,v_getdate.gdate) <= @RefreshDays RETURN @LendHouseCount
END
(
@CommunityID INT=0,
@LArea INT=0,
@UArea INT=0,
@LLendPrice FLOAT=0,
@ULendPrice FLOAT=0,
@Rooms INT=0,
@HouseType INT=0,
@RefreshDays INT=0
)
RETURNS INT
AS
BEGIN
DECLARE @LendHouseCount INT SELECT @LendHouseCount=Count(*) FROM dbo.LendHouse
INNER JOIN Building ON LendHouse.CommunityID = Building.BuildingID
full join v_getdate on 1=1
WHERE HouseState=1 and LendHouse.State=4 and AgentSourceID = 0
AND CommunityID=(CASE WHEN @CommunityID >0 THEN @CommunityID ELSE CommunityID END )
AND ISNULL(LendPrice,0)>=(CASE WHEN @ULendPrice>@LLendPrice THEN @LLendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendPrice,0)<=(CASE WHEN @ULendPrice>@LLendPrice THEN @ULendPrice ELSE ISNULL(LendPrice,0) END)
AND ISNULL(LendHouse.Area,0)>=(CASE WHEN @UArea>@LArea THEN @LArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(LendHouse.Area,0)<=(CASE WHEN @UArea>@LArea THEN @UArea ELSE ISNULL(LendHouse.Area,0) END)
AND ISNULL(Rooms,0)=(CASE WHEN @Rooms>0 THEN @Rooms ELSE ISNULL(Rooms,0) END)
AND ISNULL(HouseType,0)=(CASE WHEN @HouseType>0 THEN @HouseType ELSE ISNULL(HouseType,0) END)
and DATEDIFF(day, LendHouse.publishtime,v_getdate.gdate) <= @RefreshDays RETURN @LendHouseCount
END
2000下自定义函数和不确定性内置系统函数