SQL Server Performance

Dynamic Where Clause

Discussion in 'Getting Started' started by baburk, Jun 13, 2008.

  1. baburk New Member

    Hi,
    According to input my column changes in where condition.
    I tried it gives an error.
    in the else part ELSE CountryID = @CountryID
    Msg 102, Level 15, State 1, Line 16
    Incorrect syntax near '='.
    If i removed the else part ELSE CountryID = @CountryID
    I got this error.
    Msg 4145, Level 15, State 1, Line 18
    An expression of non-boolean type specified in a context where a condition is expected, near 'END'.

    DECLARE @PropertyName VARCHAR(100)
    DECLARE @PropertyID VARCHAR(10)
    DECLARE @CountryID INT

    BEGIN

    SET @PropertyName = ' '
    SET @PropertyID = '56'
    SET @CountryID = 3

    SELECT * FROM vw_BasicSearch
    WHERE

    CASE
    WHEN ( ISNULL(@PropertyName, ' ') != ' ' AND ISNULL(@PropertyID, ' ') != ' ') THEN ' PropertyName @PropertyName AND PropertyID = @PropertyID'
    ELSE CountryID = @CountryID
    END
    END
    Help me to over come this problem.
    Thanks in advance
  2. Adriaan New Member

    CASE is an expression, not an operator. It returns a value, it does not control the flow!
    So you can use it like ...
    WHERE column = CASE WHEN criteria THEN expression1 ELSE expression2 END
    ****
    Actually you're missing the point about dynamic SQL. You cannot have a dynamic WHERE clause in an otherwise static query statement.
    The whole query statement must be compiled as a string, and you can then execute the string.
  3. Madhivanan Moderator

    or simply use two statements based on the condition
    If condtion 1
    statement 1
    else
    statement 2
  4. Madhivanan Moderator

    Also read www.sommarskog.se/dyn-search.html
  5. myazid New Member

    try this
    BEGIN
    SET @PropertyName = ' '
    SET @PropertyID = '56'SET @CountryID = 3SELECT * FROM vw_BasicSearch WHERE (( ISNULL(@PropertyName, ' ') != ' '
    AND ISNULL(@PropertyID, ' ') != ' ') AND PropertyID = @PropertyID)or(( ISNULL(@PropertyName, ' ') ' '
    or ISNULL(@PropertyID, ' ') != ' ') AND CountryID = @CountryID)END
  6. Madhivanan Moderator

    <<
    SET @CountryID = 3SELECT * FROM vw_BasicSearch
    >>
    what do you mean by this?

Share This Page