Where condition help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Where condition help

Author

Topic

meditdba
Starting Member

7 Posts

Posted - 2009-06-11 : 14:43:25

I am creating dynamic SQL to implement this condition, but is there a way I can do this directly in SQL

declare @useBenefitAmt bit
declare @useBEnefitPct bit
declare @sql varchar(1000)

select @sql = 'WHERE '

if(@useBenefitAmt = 1 and @useBenefitPct = 1)
set @sql = @sql + ' (erse.BenefitAmt is not null or erse.BenefitPct is not null)'
else if(@useBenefitPct = 1)
set @sql = @sql + ' erse.BenefitPct is not null'
else if(@useBenefitAmt = 1)
set @sql = @sql + ' erse.BenefitAmt is not null'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-11 : 14:52:26

I did this real quick, but I think it works:

WHERE  
	(@useBenefitAmt = 1 and @useBenefitPct = 1 AND (erse.BenefitAmt IS NOT NULL OR erse.BenefitPct IS NOT NULL))
	OR (@useBenefitPct = 1 AND @useBenefitAmt = 0 AND erse.BenefitPct IS NOT NULL)
	OR (@useBenefitPct = 0 AND @useBenefitAmt = 1 AND erse.BenefitAmt IS NOT NULL)

meditdba
Starting Member

7 Posts

Posted - 2009-06-11 : 15:03:38

quote:

Originally posted by Lamprey

I did this real quick, but I think it works:

WHERE  
	(@useBenefitAmt = 1 and @useBenefitPct = 1 AND (erse.BenefitAmt IS NOT NULL OR erse.BenefitPct IS NOT NULL))
	OR (@useBenefitPct = 1 AND @useBenefitAmt = 0 AND erse.BenefitPct IS NOT NULL)
	OR (@useBenefitPct = 0 AND @useBenefitAmt = 1 AND erse.BenefitAmt IS NOT NULL)

Excellent, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources