Case in Where Statement using NULL/IS NULL

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 in Where Statement using NULL/IS NULL

Author

Topic

wmotter
Starting Member

1 Post

Posted - 2008-12-07 : 20:00:40

The following stored procedure works fine

CREATE PROCEDURE [dbo].[Test](
@InventoryID int,
@FromDate varchar(32) = '1/1/1990',
@ToDate varchar(32) = '12/31/2099')
AS
BEGIN
SET NOCOUNT ON;
SELECT tblInventoryItems.InventoryItemID AS Item#
FROM tblInventoryItems
INNER JOIN tblInvItemsTrans ON tblInventoryItems.InventoryItemID = tblInvItemsTrans.InventoryItemID
WHERE
(
Comment LIKE
CASE WHEN @FromDate = '1/1/1990' THEN '%'
ELSE 'REC%'
END
)
AND (tblInventoryItems.InventoryID = @InventoryID)
AND (InvItemTransDate >= @FromDate AND InvItemTransDate <= @ToDate)
END

When I add the second case statement below with the OR I get the error
Msg 156, Level 15, State 1, Procedure Test, Line 18
Incorrect syntax near the keyword 'CASE'.

CREATE PROCEDURE [dbo].[Test](
@InventoryID int,
@FromDate varchar(32) = '1/1/1990',
@ToDate varchar(32) = '12/31/2099')
AS
BEGIN
SET NOCOUNT ON;
SELECT tblInventoryItems.InventoryItemID AS Item#
FROM tblInventoryItems
INNER JOIN tblInvItemsTrans ON tblInventoryItems.InventoryItemID = tblInvItemsTrans.InventoryItemID
WHERE
(
Comment LIKE
CASE WHEN @FromDate = '1/1/1990' THEN '%'
ELSE 'REC%'
END
OR Comment IS
CASE WHEN @FromDate = '1/1/1990' THEN NULL
END
)
AND (tblInventoryItems.InventoryID = @InventoryID)
AND (InvItemTransDate >= @FromDate AND InvItemTransDate <= @ToDate)
END

What have I done wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:35:30

[code]CREATE PROCEDURE [dbo].[Test](
@InventoryID int,
@FromDate varchar(32) = '1/1/1990',
@ToDate varchar(32) = '12/31/2099')
AS
BEGIN
SET NOCOUNT ON;
SELECT tblInventoryItems.InventoryItemID AS Item#
FROM tblInventoryItems
INNER JOIN tblInvItemsTrans ON tblInventoryItems.InventoryItemID = tblInvItemsTrans.InventoryItemID
WHERE
(
Comment LIKE
CASE WHEN @FromDate = '1/1/1990' THEN '%'
ELSE 'REC%'
END
OR (Comment IS NULL AND @FromDate = '1/1/1990')
)
AND (tblInventoryItems.InventoryID = @InventoryID)
AND (InvItemTransDate >= @FromDate AND InvItemTransDate <= @ToDate)
END[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources