CASE statement on a WHERE clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE statement on a WHERE clause?

Author

Topic

vicpal25
Starting Member

21 Posts

Posted - 2008-05-08 : 11:03:10

I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error:

Line 15: Incorrect syntax near '='.

this is my query that lies on a PROC:

SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 11:08:32

Both sides of the condition are the same, but apart from that, the syntax is incorrect. This would work:


a.Item_No = case when @v_Rev_Lett is not null then @v_Rev_Lett else @v_Rev_Lett end

vicpal25
Starting Member

21 Posts

Posted - 2008-05-08 : 11:11:43

Yeah, I dont know why it is throwing that exception. Here is my current proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[csp_engr_copy_assembly]
@v_Bill_Type varchar(2),
@v_Item_No varchar(20),
@v_Series_Lett char(6)=NULL,
@v_Rev_Lett char(6)=NULL
AS
SET NOCOUNT ON

SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end

SET NOCOUNT OFF
GO

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 11:14:02

Please read my last reply properly. The syntax is incorrect and the syntax I gave you would work.

You will have a problem handling nulls though.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 11:21:09

It makes no sense

SELECT		count(a.pEngr_Item_ID) as assembly_count
FROM		Engr_Item a
INNER JOIN	Engr_BOM_Control b ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE		b.Bill_Type = @v_Bill_Type
		and a.Item_No = @v_Item_No
		and a.Item_No =	case
					when @v_Rev_Lett is null then @v_Rev_Lett
					else @v_Rev_Lett
				end

@v_Rev_Lett will always be compared against a.Item_No.
There is a flaw in your logic primarly and your code second.

E 12°55'05.25"
N 56°04'39.16"

vicpal25
Starting Member

21 Posts

Posted - 2008-05-08 : 11:26:04

yeah sorry I forgot to change the value:

SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=null end

Im still getting that error though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 11:32:00

quote:
Originally posted by vicpal25

yeah sorry I forgot to change the value:

SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
a.Item_No=case when @v_Rev_Lett is not null then ~~a.Item_No=~~@v_Rev_Lett else ~~a.Item_No=~~null end

Im still getting that error though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources