Dot Net Developer Dot Net Developer - 1 month ago 10
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'))
Begin
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)
Begin
set @strSQLClause ='AND QuestionCd!=Q8'
End
Else
set @strSQLClause =''
End


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

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 ')