Using IF in where clause

Question

Post reply

Using IF in where clause

joe-584802

SSC Eights!

Points: 949
More actions
December 10, 2015 at 12:42 am

#306997

In the following SP I only want to use the @role parameter if it's value is > 0 I've tried using an IF condition but it doesn't like that and I get an error Incorrect syntax near the keyword 'AND'.
Can someone let me know where I'm going wrong.
TIA
ALTER PROCEDURE [dbo].[GetBPData3]
(
@criteria varchar(200),
@Role int,
@status int
)
AS
SELECT *
FROM tblBusinessPartner t1
WHERE NOT EXISTS
(
SELECT BPID
FROM tblBusinessPartner t2
CROSS APPLY dbo.delimitedsplit8k(@criteria,',')
WHERE isnull(t2.PartnerName,'')+isnull(t2.Address,'') NOT LIKE '%'+item+'%'
AND t1.BPID = t2.BPID
) AND t1.BPStatusID = case when @status=0 then t1.BPStatusID else @status end
IF @Role > 0 Begin
AND t1.Role1 = case when @Role=1 then 1 else 0 end
AND t1.Role2 = case when @Role=2 then 1 else 0 end
AND t1.Role3 = case when @Role=3 then 1 else 0 end
AND t1.Role4 = case when @Role=4 then 1 else 0 end
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Eirikur Eiriksson

SSC Guru

Points: 182911

December 10, 2015 at 1:21 am

#1845111

Quick suggestion, use CASE instead of IF

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

IF is a control flow statement. It's used outside of queries to determine whether a statement should run or not. It can't be used inside a query.

You probably want something like

SELECT *

FROM tblBusinessPartner t1

WHERE NOT EXISTS

(

SELECT BPID

FROM tblBusinessPartner t2

CROSS APPLY dbo.delimitedsplit8k(@criteria,',')

WHERE isnull(t2.PartnerName,'')+isnull(t2.Address,'') NOT LIKE '%'+item+'%'

AND t1.BPID = t2.BPID

) AND t1.BPStatusID = case when @status=0 then t1.BPStatusID else @status end

AND (t1.Role1 = case when @Role=1 then 1 else 0 end OR @Role = 0)

AND (t1.Role2 = case when @Role=2 then 1 else 0 end OR @Role = 0)

AND (t1.Role3 = case when @Role=3 then 1 else 0 end OR @Role = 0)

AND (t1.Role4 = case when @Role=4 then 1 else 0 end OR @Role = 0)

END

And then please read at least the first section of https://www.simple-talk.com/content/article.aspx?article=2280

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

joe-584802 SSC Eights! Points: 949 More actions · Answer 3

Cheers Gail that's great, thanks.

I'll read through that article. I'm coming from an Access background so its a whole new ball game but I'm loving it!

Joe

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Just worth noting, in SQL 2012+ there are two "IF" statements.

There's IF which is part of the flow control language and can't be used inside a DML query and there's the new (new in 2012) IIF which is very similar to a CASE statement. Note the examples below:

-- Flow control IF

IF 1=1

SELECT 'yep'

ELSE

SELECT 'nope';

-- DML IIF

SELECT IIF(1=1, 'yep', 'nope');

In the query you posted you would want to use IIF, not IF.

That said, I agree that CASE is the way to go.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001