CODEWITHSUNDEEP
sqlsql-server
Raju
Raju

Reputation: 13

SQL Server 2014: Either selected or all values in where condition

Below is what I am trying to achieve. I have a procedure which receives employeeIds as optional arguments and stores them into a temp table (temp_table) like this

empId
-------
3432
3255
5235
2434

Now I need to run below query in 2 conditions:

1st condition: if argument is non blank then my query should be-

SELECT * 
FROM DEPARTMENTS 
INNER JOIN temp_table ON emp_no = empId

2nd condition: if argument is blank it will take all the rows from department table

SELECT * 
FROM DEPARTMENTS

One option I can use is:

IF (@args <> '')
BEGIN
    SELECT *  
    FROM DEPARTMENTS 
    INNER JOIN temp_table ON emp_no = empId
END
ELSE
BEGIN
    SELECT * 
    FROM DEPARTMENTS 
END

But I am looking for a better option where I don't need to write almost same query twice. Please help.

Upvotes: 1

Views: 34

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

I recommend to stick to what you are already doing.
It is the cleanest and safest way performance wise.

Upvotes: 3

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Try this one

SELECT  * 
FROM    DEPARTMENTS
WHERE   (
        @args <> ''
    OR  EXISTS  (SELECT 1 FROM temp_table WHERE emp_no = empId)
)

Upvotes: 1

Related Questions