Help with Dynameic SQL

  • I am trying to generate a dynamic SQL string based on the following:

    DECLARE@ConsultantIDnVarChar(50)

    DECLARE@AchieveLevelStartint

    DECLARE@AchieveLevelEndint

    DEclare @Level10bit

    Declare @Level20bit

    Declare @Level30bit

    Declare @Level40bit

    Declare @Level50bit

    DECLARE@SQLStrNVARCHAR(4000)

    ,@SQLLevelNVARCHAR(4000)

    ,@PeriodDateDateTime

    ,@ORStrNvarchar(50)

    ,@StateSQLnvarchar(50)

    Set @Level10 = 1

    Set @Level20 = 1

    Set @Level30 = 0

    Set @Level40 = 0

    Set @Level50 = 0

    There is some table manipulation below this but when I want to generate the final Dynamic

    This is the clause that puts it all together.

    SELECT @SQLStr

    -- All Active Filter

    IF (@Status = 'All active')

    BEGIN

    SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '

    SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'

    SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr

    SELECT @SQLstr

    END

    This is the string that is generated.

    SELECT * FROM #DLFiltered WHERE Active = 1 AND DownlineLevel BETWEEN 1 AND 3 OR (DownlineLevel = 0) AND (CurrentLevelXID = 10 AND 1=1 OR (CurrentLevelXID = 20 AND 1=1) OR (CurrentLevelXID = 30 AND 0=1) OR (CurrentLevelXID = 40 AND 0=1) OR (CurrentLevelXID = 50 AND 0=1) OR (CurrentLevelXID = 60 AND 1=1) OR (CurrentLevelXID = 70 AND 1=1) OR (CurrentLevelXID = 80 AND 1=1)) AND MonthToDate_Total BETWEEN 0 AND 2000 OR (ConsultantID IS NULL)

    But the problem is when it executes it still returning all the CurrentLevelXID and not just the ones the @LEVEL = 1 if the @Level = 0 they should be omitted.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • I suggest you read these two articles on dynamic SQL by Erland Sommarskog:

    http://www.sommarskog.se/dynamic_sql.html

    http://www.sommarskog.se/dyn-search.html

    In fact, I think you should read all of Erland's articles, but you should at least start with these two.

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • It almost sounds like the query it self is not working the way you want it to work. Anyway the few times I have to use dynamic SQL (very few) I tend to do it one row at a time to make sure it works as expected.

    The more you are prepared, the less you need it.

  • CurrentLevelXID is of the varchar type. You have to use single quotes in WHERE condition. Otherwise, something like (CurrentLevelXID = 70 AND 1=0) will return TRUE.

    Try to debug the SQL expression you received as a result of dynamic SQL to receive expected results and then edit your dynamic SQL accordingly.

  • Glen (8/28/2008)


    CurrentLevelXID is of the varchar type. You have to use single quotes in WHERE condition. Otherwise, something like (CurrentLevelXID = 70 AND 1=0) will return TRUE.

    Try to debug the SQL expression you received as a result of dynamic SQL to receive expected results and then edit your dynamic SQL accordingly.

    Khm... Can you provide an example?

    You're implying that 1 can under certain circumstances be equal to 0. I'm sure I'm not the only one who would like to know what those circumstances are. 🙂

    Just in case:

    http://msdn.microsoft.com/en-us/library/ms188372.aspx

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • I adore sarcasm, but I was hoping that the example I provided was clear?

    Of course I might be wrong, but what I meant was:

    I understood that you are using this construction (CurrentLevelXID = 70 AND 1=0) to filter the value of CurrentLevelXID = 70?

    If that is correct and CurrentLevelXID is varchar data type, then

    (CurrentLevelXID = 70 AND 1=0) will always return FALSE, and the value of '70' for CurrentLevelXID will appear in your resultset (TRUE)?

  • When I build dynamic SQL, I always start by writing it out by hand and formatting it properly to make sure it is exactly what I want. Only then do I try to convert it into dynamic code. Looking at what your code produces and reformatting it, I am pretty sure that it is not what you were wanting. I believe that your OR's are messing up your logic. Nothing else matters if either Downlinelevel is zero or ConsultantID is null. Those OR's will win. If neither one of those is the case, then all your outer AND's will need to be true, but you still may have a problem with the second AND. Without appropriate parentheses to control your boolean logic you can get very bad results.

    SELECT *

    FROM #DLFiltered

    WHERE Active = 1

    AND DownlineLevel BETWEEN 1 AND 3

    OR (DownlineLevel = 0)

    AND (CurrentLevelXID = 10

    AND 1=1

    OR (CurrentLevelXID = 20 AND 1=1)

    OR (CurrentLevelXID = 30 AND 0=1)

    OR (CurrentLevelXID = 40 AND 0=1)

    OR (CurrentLevelXID = 50 AND 0=1)

    OR (CurrentLevelXID = 60 AND 1=1)

    OR (CurrentLevelXID = 70 AND 1=1)

    OR (CurrentLevelXID = 80 AND 1=1)

    )

    AND MonthToDate_Total BETWEEN 0 AND 2000

    OR (ConsultantID IS NULL)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply