Conditional SQL statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Conditional SQL statement

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-08-27 : 11:32:49

Hi all:

I want conditional where clause but getting error on below sql code. Can you please suggest me where I am wrong

WHERE LINK.pr_grp_id = 1
AND CUST.active = @IsActive
if(@IsAssigned = 1)
AND CUST.prid NOT IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '')
end
else if(@IsAssigned = 0)
AND CUST.prid IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '')
end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-27 : 11:42:09

Try this:

WHERE LINK.pr_grp_id = 1
AND CUST.active = @IsActive
AND
(
(@IsAssigned = 1 AND CUST.prid NOT IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '') ) OR
(@IsAssigned = 0 AND CUST.prid IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '') )
)

No, you're never too old to Yak'n'Roll if you're too young to die.

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-08-27 : 14:14:20

Hi Webfred:

It didnt work. I have three values in @IsAssigned paramter. 1,2,3. if value of @IsAssigned = 1 I want
following clause added in where

CUST.prid NOT IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '')

if @IsAssigned = 2, I want following query to run:

CUST.prid IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> ''

if value of @IsAssigned = 3

then nothing added in the where clause and following is last statement of where clause

AND CUST.active = @IsActive

Regards,
Asif Hameed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-27 : 14:17:25

Try this:

WHERE LINK.pr_grp_id = 1
AND CUST.active = @IsActive
AND
(
(@IsAssigned = 1 AND CUST.prid NOT IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '') ) OR
(@IsAssigned = 2 AND CUST.prid IN (SELECT Commerce_CustPRID FROM Commerce_Customer_SalesRep WHERE Commerce_RepPRID <> '') ) OR
(@IsAssigned = 3)
)

No, you're never too old to Yak'n'Roll if you're too young to die.

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-08-27 : 14:34:27

you rock :) thanks. it worked. thanks for very good and quick response.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-27 : 15:50:56

welcome