CODEWITHSUNDEEP
sqlsql-server-2008where-clause
Developer
Developer

Reputation: 888

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.

Upvotes: 0

Views: 506

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 1

GarethD
GarethD

Reputation: 69819

You would need to use dynamic SQL, but why not just have two statements that execute SQL, so rather than set @strSQLClause = 'AND ...', simply have a select statement here with the condition(s) you need

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 
            INNER JOIN HOSeleMonthsOccupied sm 
                ON sr.MonthsOccupied=sm.MonthsOccupiedCd
    WHERE   AppId=@AppId;

    IF(CONVERT(INT,LTRIM(RTRIM(@strMonthsOccupied))) > 9)
    BEGIN
        SELECT  * 
        FROM    SHSeleQuestions 
        WHERE   MasterCompanyId='09' 
        AND     QuestionCd!='Q8';
        RETURN;
    END
END

SELECT  * 
FROM    SHSeleQuestions 
WHERE   MasterCompanyId='09';

That being said, there are so many issues with the above I don't really know where to begin. You declare your variable then assign it an indeterminate value:

DECLARE @strMonthsOccupied CHAR(1)

SELECT  DISTINCT @strMonthsOccupied = sm.MonthsOccupiedDesc 
FROM    HOStructureRating sr 
        INNER JOIN HOSeleMonthsOccupied sm 
            ON sr.MonthsOccupied=sm.MonthsOccupiedCd
WHERE   AppId=@AppId;

If the query returns multiple rows then there is no clear logic for which value the variable should be assigned. The nex issue is that this CHAR(1) variable is clearly a number based on your attempted conversion:

IF(CONVERT(INT,LTRIM(RTRIM(@strMonthsOccupied))) > 9)

Why not just cut out the middle man and declare an INT to begin with. The next point is that it is a CHAR(1) so isn't actually big enough to store anything greater than 9, so your above condition will never be true.

Even if sm.MonthsOccupiedDesc, was 10, the variable would simply be truncated to 1, which is smaller than 9, so fails the condition, e.g.

DECLARE @strMonthsOccupied CHAR(1) = '10';
IF(CONVERT(INT,LTRIM(RTRIM(@strMonthsOccupied))) > 9)
    PRINT 'TRUE';
ELSE
    PRINT 'FALSE';

Upvotes: 0

Related Questions