Post reply

Adding a Dynamic AND Clause to a Where Statement

  • December 21, 2007 at 10:42 am

    #180019

    I have the following code:

    DECLARE@ConsultantIDnVarChar(50)

    ,@PeriodDateDateTime

    ,@AchieveLevel int

    ,@Line varchar (10)

    ,@Status varchar(10)

    ,@GreaterThan Decimal

    ,@LessThanDecimal

    ,@EqualToDecimal

    ,@AndVARCHAR(200)

    SET @ConsultantID = '0000344'

    SET @PeriodDate = GETDATE()

    SET @AchieveLevel = 20

    SET @Line='1,2,3'

    SET @status = 'Active' -- 'Inactive', 'All'

    SET @AND = NULL

    -- Set up Where clause for Sales amount filter.

    IF (@GreaterThan IS NOT NULL)

    SELECT @And = 'AND o.partOnetotal >= ' + @GreaterThan + ' '

    ELSE IF (@LessThan IS NOT NULL)

    SELECT @And = 'AND o.partOnetotal >= ' + @LessThan + ' '

    ELSE IF (@EqualTo IS NOT NULL)

    SELECT @And = 'AND o.partOnetotal >= ' + @EqualTo + ' '

    -- Active Filter

    IF (@Status = 'Active')

    SELECT * FROM #DLFiltered WHERE Active = 1

    AND CurrentLevelXID >= @AchieveLevel

    AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))

    + @AND

    ELSE IF (@Status = 'Inactive')

    SELECT * FROM #DLfiltered WHERE Active = 0

    AND CurrentLevelXID >= @AchieveLevel

    AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))

    ELSE IF (@Status = 'All')

    SELECT * FROM #DLfiltered WHERE Active In (1,0)

    AND CurrentLevelXID >= @AchieveLevel

    AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))

    When I do this I get the following error:

    Msg 102, Level 15, State 1, Line 175

    Incorrect syntax near '+'.

    I need to pend the contents of @And to the Where clause:

    IF (@Status = 'Active')

    SELECT * FROM #DLFiltered WHERE Active = 1

    AND CurrentLevelXID >= @AchieveLevel

    AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(@Line))

    + @AND

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

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

  • December 21, 2007 at 10:47 am

    #762213

    Cant you use Dynamic SQL? Create the string with your Select statement based upon the criterea and the execute it?

    Please check for SQL Injection if you plan to use dynamic SQL.

    -Roy

  • December 21, 2007 at 10:50 am

    #762216

    How would I do that? Can you show me an example?

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

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

  • December 21, 2007 at 11:03 am

    #762222

    From BOL.

    USE AdventureWorks;

    GO

    DECLARE @SQLString NVARCHAR(500);

    /* Set column list. CHAR(13) is a carriage return, line feed.*/

    SET @SQLString = N'SELECT FirstName, LastName, JobTitle' + CHAR(13);

    /* Set FROM clause with carriage return, line feed. */

    SET @SQLString = @SQLString + N'FROM HumanResources.vEmployee' + CHAR(13);

    /* Set WHERE clause. */

    SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%''';

    EXEC sp_executesql @SQLString;

    GO

    -Roy

  • December 21, 2007 at 11:27 am

    #762236

    Tam trying this:

    IF (@Status = 'Active')

    SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1'+ CHAR(13);

    SET @SQLStr = @SQLStr + ' AND CurrentLevelXID >= ' + @AchieveLevel + ' '+ CHAR(13);

    SET @SQlstr = @SQlstr + 'AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(' + @Line +'))'+ @AND

    SELECT @SQLstr

    EXEC sp_executesql @SQLstr;

    And I am recieving this error:

    Msg 245, Level 16, State 1, Line 174

    Conversion failed when converting the nvarchar value 'SELECT * FROM #DLFiltered WHERE Active = 1

    AND CurrentLevelXID >= ' to data type int.

    Any ideas?

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

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

  • December 21, 2007 at 12:01 pm

    #762266

    Without the rest of your code, it's hard to say for sure what all problems you might have, but I'm guessing that your @AchieveLevel variable is an int. If so, cast it inline so that the string concatenation will be happy.

  • December 21, 2007 at 12:04 pm

    #762269

    That is what I figured out.

    IF (@Status = 'Active')

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

    SET @SQLStr = @SQLStr + ' AND CurrentLevelXID >= ' + CONVERT(Varchar(2),@AchieveLevel )

    SET @SQlstr = @SQlstr + 'AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(' + @Line +'))'+ @AND

    SELECT @SQLstr

    EXEC sp_executesql @SQLstr;

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

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

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

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