CASE: cannot test for empty string

[poolman]

Hello,
In a SELECT statement I want to add SQL to the WHERE clause depending of the value of a variable. If it is empty, do nothing. Else attach the value to the where clause.
have this
...

WHERE
    1=1
    SELECT
        CASE @employee_name
            WHEN !='' THEN 'AND e.naam = @employee_name'
    END
RETURN
 

SQL 2005 won't let me do this. I get an "Incorrect syntax near !" Also operators <> and NOT don't work.If I test for equality (WHEN ='' THEN ...) then I get no errors. How can I do this in SQL 2005?

[scripto]

Hello,
In a SELECT statement I want to add SQL to the WHERE clause depending of the value of a variable. If it is empty, do nothing. Else attach the value to the where clause.
have this
...

WHERE
    1=1
    SELECT
        CASE @employee_name
            WHEN !='' THEN 'AND e.naam = @employee_name'
    END
RETURN
 

SQL 2005 won't let me do this. I get an "Incorrect syntax near !" Also operators <> and NOT don't work.If I test for equality (WHEN ='' THEN ...) then I get no errors. How can I do this in SQL 2005?

case @employee_name
when = '' Then ''
else 'AND e.naam = @employee_name'
end

[NeoPa]

Does that mean the SELECT CASE construct only works with the ELSE clause? It must be the full construct with no optional parts?

[poolman]

case @employee_name
when = '' Then ''
else 'AND e.naam = @employee_name'
end

This is what I tried also. It gives me all rows in the table-not the one for which "AND e.naam = @employee_name". The VS2005 output window shows the parameter has a value:

Running [dbo].[spgetdepartmenttimesheets] ( @employeeNaam = Janssen ).

Somehow the additional WHERE clause I want to add via the CASE statement is ignored. Isn't there a way to view the generated SQL that's used to query the database so I can check what's actually being executed?

[scripto]

Hello,
In a SELECT statement I want to add SQL to the WHERE clause depending of the value of a variable. If it is empty, do nothing. Else attach the value to the where clause.
have this
...

WHERE
    1=1
    SELECT
        CASE @employee_name
            WHEN !='' THEN 'AND e.naam = @employee_name'
    END
RETURN
 

SQL 2005 won't let me do this. I get an "Incorrect syntax near !" Also operators <> and NOT don't work.If I test for equality (WHEN ='' THEN ...) then I get no errors. How can I do this in SQL 2005?

Usually Case statments are in the select clause not the WHERE clause, so what you can do is place a condition like

if @employee_name <> ''
select .... where .... AND e.naam = @employee_name'
else
select .... where .....