Adding dynamic condition in where clause

Developer

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.

Jaydip Jadhav

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

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-04-12

Comments

0 comments

Linq: Adding the Where clause "or" condition

Sequelize build dynamic where clause condition with AND and OR clause

adding a condition on WHERE clause of a multi lined query

MSSQL - Adding condition on the where and on clause performance

linq dynamic where clause with or condition on a INT column

Adding dynamic condition in where clause

Adding dynamic condition in where clause

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

Emulator wrong screen resolution in Android Studio 1.3

3D Touch Peek Swipe Like Mail

Double spacing in rmarkdown pdf

Svchost high CPU from Microsoft.BingWeather app errors

How to how increase/decrease compared to adjacent cell

Using Response.Redirect with Friendly URLS in ASP.NET

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

BigQuery - concatenate ignoring NULL

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Can a 32-bit antivirus program protect you from 64-bit threats

Make a B+ Tree concurrent thread safe

Bootstrap 5 Static Modal Still Closes when I Click Outside

Vector input in shiny R and then use it

Assembly definition can't resolve namespaces from external packages