case-when error

[philip]

hello i am learning how to write stored procedures in sql server, i
would like to know what's wrong with the following statement? please
help



the management studio gives me the following error:
Msg 156, Level 15, State 1, Procedure spDealMasterSea rch, Line 19
Incorrect syntax near the keyword 'CASE'.



USE [mydatabase]
GO
/****** Object: StoredProcedure [dbo].[spHouseUpdate] Script Date:
04/15/2008 16:02:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
CREATE PROCEDURE [dbo].[spDealMasterSea rch]

@in_Location varchar(50),
@in_HouseType varchar(50),
@in_Size varchar(50),
@in_ExpectedPri ce varchar(50)

AS
BEGIN

SELECT dealMasterId From
dealmaster INNER JOIN houses
ON dealmaster.Hous eId = houses.HouseId
WHERE
houses.Location = @in_Location AND
houses.HouseTyp e = @in_HouseType AND
houses.Size

CASE
WHEN @in_Size = 'within1000' THEN <= 1000
WHEN @in_Size = 'more1000' THEN 1000
ELSE 0
END AND

houses.expected Price
CASE
WHEN @in_ExpectedPri ce = 'within5m' THEN <= 5
WHEN @in_ExpectedPri ce = 'within10m' THEN <=10
WHEN @in_ExpectedPri ce = 'dontcare' THEN 0
ELSE 0
END AND

houses.EntitySt atus = 'A' AND
dealmaster.Enti tyStatus = 'A' AND
dealmaster.expe ctedPrice <= 5 AND
dealmaster.stat us = 'offering';
END

[Tom van Stiphout]

Re: case-when error

On Tue, 15 Apr 2008 02:27:31 -0700 (PDT), philip <majorone@gmail .com>
wrote:

CASE cannot be used in a WHERE clause.
-Tom.

>hello i am learning how to write stored procedures in sql server, i
>would like to know what's wrong with the following statement? please
>help
>
>
>
>the management studio gives me the following error:
>Msg 156, Level 15, State 1, Procedure spDealMasterSea rch, Line 19
>Incorrect syntax near the keyword 'CASE'.
>
>
>
>USE [mydatabase]
>GO
>/****** Object: StoredProcedure [dbo].[spHouseUpdate] Script Date:
>04/15/2008 16:02:50 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFI ER ON
>GO
>CREATE PROCEDURE [dbo].[spDealMasterSea rch]
>
>@in_Location varchar(50),
>@in_HouseTyp e varchar(50),
>@in_Size varchar(50),
>@in_ExpectedPr ice varchar(50)
>
>AS
>BEGIN
>
> SELECT dealMasterId From
> dealmaster INNER JOIN houses
> ON dealmaster.Hous eId = houses.HouseId
> WHERE
> houses.Location = @in_Location AND
> houses.HouseTyp e = @in_HouseType AND
> houses.Size
>
> CASE
> WHEN @in_Size = 'within1000' THEN <= 1000
> WHEN @in_Size = 'more1000' THEN 1000
> ELSE 0
> END AND
>
> houses.expected Price
> CASE
> WHEN @in_ExpectedPri ce = 'within5m' THEN <= 5
> WHEN @in_ExpectedPri ce = 'within10m' THEN <=10
> WHEN @in_ExpectedPri ce = 'dontcare' THEN 0
> ELSE 0
> END AND
>
> houses.EntitySt atus = 'A' AND
> dealmaster.Enti tyStatus = 'A' AND
> dealmaster.expe ctedPrice <= 5 AND
> dealmaster.stat us = 'offering';
>END

[Plamen Ratchev]

Re: case-when error

CASE returns an expression, not logical condition. Here is how you can
change your query:

SELECT dealMasterId
FROM dealmaster AS D
INNER JOIN houses AS H
ON D.HouseId = H.HouseId
WHERE H.Location = @in_Location
AND H.HouseType = @in_HouseType
AND CASE WHEN @in_Size = 'within1000'
THEN CASE WHEN H.Size <= 1000 THEN 'T' END
WHEN @in_Size = 'more1000'
THEN CASE WHEN H.Size 1000 THEN 'T' END
ELSE CASE WHEN H.size 0 THEN 'T' END
END = 'T'
AND CASE WHEN @in_ExpectedPri ce = 'within5m'
THEN CASE WHEN H.expectedPrice <= 5 THEN 'T' END
WHEN @in_ExpectedPri ce = 'within10m'
THEN CASE WHEN H.expectedPrice <= 10 THEN 'T' END
WHEN @in_ExpectedPri ce = 'dontcare'
THEN CASE WHEN H.expectedPrice 0 THEN 'T' END
ELSE CASE WHEN H.expectedPrice 0 THEN 'T' END
END = 'T'
AND H.EntityStatus = 'A'
AND D.EntityStatus = 'A'
AND D.expectedPrice <= 5
AND D.status = 'offering';


HTH,

Plamen Ratchev

http://www.SQLStudio.com