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
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.
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