Dot Net Developer Dot Net Developer - 1 year ago 75
SQL Question

Adding dynamic condition in where clause

I want to add condition in 'where' clause depends upon 'if' condition like below

Declare @strSQLClause varchar(50)
If (@QMasterCompanyId='09' and @State='FL' and (@LOB='HO' or @LOB='HP'))
Declare @strMonthsOccupied char(1)
select Distinct @strMonthsOccupied=sm.MonthsOccupiedDesc from HOStructureRating sr
join HOSeleMonthsOccupied sm on sr.MonthsOccupied=sm.MonthsOccupiedCd
where AppId=@AppId
If(CONVERT(int,LTRIM(RTrim(@strMonthsOccupied))) > 9)
set @strSQLClause ='AND QuestionCd!=Q8'
set @strSQLClause =''

so that in my Query will work as

select * from SHSeleQuestions where MasterCompanyId='09' + @strSQLClause

But this approach is not working fine, can anyone please help me on this.

Answer Source

There are two ways to do this one is use dynamic sql or other one is below one :

select * 
from SHSeleQuestions 
where  MasterCompanyId='09'  AND
       1 = CASE WHEN LEN(@strSQLClause) > 0 AND QuestionCd != 'Q8' THEN 1 
                WHEN LEN(@strSQLClause) = 0 THEN 1 END 

Using Dynamic SQL

EXEC('select * from SHSeleQuestions where  MasterCompanyId=''09'''  + @strSQLClause ')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download