If then else in the Where clause

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)

If then else in the Where clause

Author

Topic

chiman

21 Posts

Posted - 2007-08-20 : 12:41:34

Hello
I have a query some thing like

declare @DocStatus int
declare @Storeid int

Select @Storeid = 4963
Select @DocStatus = 1

select d.DocumentNumber as [Delivery number]
from dbo.Document d
where d.SourceID = @StoreId
and DocStatus_ID in
if @DocStatus=1 then
select Codelist_ID from Codelist inner join Codes on Codes.Code_ID = Codelist.code_Id and Codes.Code_ID=2 and CodeList_ID<>3
else
select Codelist_ID from Codelist inner join Codes on Codes.Code_ID = Codelist.code_Id and Codes.Code_ID=2 and CodeList_ID=3

Here depending on the DocStatus_ID parameter, i want to filter my where clause.
This is giving an error saying-- Incorrect syntax near the "If" keyword.

Kindly help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 12:51:19

[code]DECLARE @DocStatus INT,
@Storeid INT

SELECT @Storeid = 4963,
@DocStatus = 1

SELECT d.DocumentNumber as [Delivery number]
FROM dbo.Document AS d
INNER JOIN Codelist AS cl ON cl.Codelist_ID = d.DocStatus_ID
INNER JOIN Codes AS c ON c.Code_ID = cl.code_Id
WHERE d.SourceID = @StoreId
AND c.Code_ID = 2
AND 1 = CASE
WHEN @DocStatus = 1 AND cl.CodeList_ID <> 3 THEN 1
WHEN cl.CodeList_ID = 3 THEN 1
ELSE 0
END[/code]

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

chiman

21 Posts

Posted - 2007-08-20 : 13:00:27

Thanks this seems to be working. Could you spare some time and explain these
AND 1 = CASE
WHEN @DocStatus = 1 AND cl.CodeList_ID <> 3 THEN 1
WHEN cl.CodeList_ID = 3 THEN 1
ELSE 0
END

Thanks once agin.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 13:23:01

It is a check.
If @DocStatus = 1 AND cl.CodeList <> 3, I set a "flag" to 1.
If cl.CodeList_ID = 3 then I set the same "flag" to 1.
If neither are true, I set the "flag" to 0.

I then check if the "flag" value is equal to 1.

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

chiman

21 Posts

Posted - 2007-08-20 : 13:34:42

thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources