using dynamic CASE statements with 'WHERE'

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 2000 Forums

SQL Server Development (2000)

using dynamic CASE statements with 'WHERE'

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-24 : 09:34:35

Peter writes "SQL Server 2000 (Desktop), Win XP Pro SP2
Keep getting an error on function:
"ADO ERROR: Incorrect systax line 13 near '='

I just can't seem to get the syntax right for the WHERE statement combined with a CASE statement and my parameter @ManufacturerID

My intention with the query is to show all rows if @ManufacturerID = 0, otherwise limit rows to ManufacturerID=@ManufacturerID

My thanks in advance..

ALTER FUNCTION dbo.fnCustomerHistory
(@ManufacturerID int)
RETURNS TABLE
AS
RETURN ( SELECT PreferredCustomerID, ManufacturerID, SaleComplete, ProductDescription

FROM dbo.qryPurchaseItem

WHERE
CASE
WHEN @ManufacturerID = 0 THEN
ManufacturerID = ManufacturerID
ELSE
ManufacturerID = @ManufacturerID
END )"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-24 : 09:50:18

change your where to

where ManufacturerID  = @ManufacturerID
or    @ManufacturerID = 0

----------------------------------
'KH'
It is inevitable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources