use IF in WHERE clause?

[poolman]

Hi,
I want to buld te where clause of a select statement dynamically. But SQL2005 won't let me do this ("An object or column name is missing or empty"etc). What am I doing wrong here?

Thanks!

SELECT
        d.naam,
        e.naam,
        h.ts_week,
        h.ts_jaar,
        l.nummer
FROM
        departments d
        JOIN emp_dep ON d.id=emp_dep.dep_id
        JOIN employees e ON emp_dep.emp_id=e.id
        JOIN hours h ON e.id=h.employee_id
        JOIN locations l ON h.locatie_id=l.id
WHERE
        1=1
        IF @employee_name !=""
            AND e.naam=@employee_name
        IF @department_name!="" 
            AND d.naam=@department_name
        IF @timesheet_week !=""
            AND h.ts_week=@timesheet_week
        IF @timesheet_year !=""
            AND h.ts_jaar=@timesheet_year
        IF @location_nummer !=""
            AND l.nummer=@location_nummer

[scripto]

Use the Case statement in the SELECT clause that's how it's done.

for example:

select case @Employee when 'SAM' THEN 1
when 'SUE' THEN 2
else '' end as EmployeeName, date, class
from mytable

[poolman]

Thanks! I found out how to.

[serutan]

Once you post a problem on a tech forum, it's very valuable to other people if you take the time to post the solution, even if you found it yourself. I see so many threads like this that end with "Never mind, I figured it out." This just creates blind alleys for people who are having the same problem.

[iburyak]

Try this:

SELECT
        d.naam,
        e.naam,
        h.ts_week,
        h.ts_jaar,
        l.nummer
FROM
        departments d
        JOIN emp_dep ON d.id=emp_dep.dep_id
        JOIN employees e ON emp_dep.emp_id=e.id
        JOIN hours h ON e.id=h.employee_id
        JOIN locations l ON h.locatie_id=l.id
WHERE
       e.naam like '%' + @employee_name
       AND d.naam like % + @department_name
       AND h.ts_week like % + @timesheet_week
       AND h.ts_jaar like '%' + @timesheet_year
       AND l.nummer like '%' + @location_nummer

Let's examine this:
e.naam like '%' + @employee_name

In this case if @employee_name = ""
e.naam like '%' + ""
the same as

e.naam like '%' - means everything

If @employee_name = "poolman"
e.naam like '%' + "poolman"
the same as
e.naam like '%poolname"
So if name is passed it will be found and if nothig passed all namew will be returned.

Good Luck.

[swils08]

Just want to say Serutan, I agree .. I stumbled across this thread looking for answers. And like so many other threads that just fizzle out with "S'ok, I figured it out"... this one left me frustrated. I had a similar problem and for others with the same problem as this.. the solution is to use single quotes - not double quotes. In the xample above..

IF @employee_name !="" ... should be ... IF @employee_name !=''

In my case it was declaring tsql variables .. like

@Postcode varchar(10) = "" ... when it should be ... @Postcode varchar(10) = '',

Hope this helps other people as its not an obvious one to spot (I wasn't even writing an insert query so SQL error message really didn't help me!)

Cheers