CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
VAAA
VAAA

Reputation: 15049

Where clause with conditionals in stored procedure

I have the following query in my stored procedure (SQL Server):

SELECT 
    ea.attendanceid,
    ea.employeeid,

    FROM employee_attendance ea

    WHERE 
    ea.masterentity = @masterentity
    AND ea.employeeid = @employeeid

    -- here I need to know what value @type is and then create my conditionals
    AND (ISNULL(@type,'') = '' OR 
        .......

My parameter @type has diferent string values:

And depending on the value is the column that I have to filter:

If `@type='IL'` then I have to filter: `AND ea.lunchtype = 'C'`
If `@type='EL'` then I have to filter: `AND ea.lunchtype = 'CI'`
If `@type='L'` then I have to filter: `AND ea.islunch = 1`
If `@type='N'` then I have to filter: `AND ea.islunch = 0`
If `@type='R'` then I have to filter: `AND (ea.ismanual = 1 AND ea.lunchtype = 'K')`

Any clue on how to accomplish this?

Upvotes: 0

Views: 81

Answers (2)

anon
anon

Reputation:

Use a CASE statement.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

MSDN CASE (Transact-SQL)

Refer to the examples on this page on howto.

Upvotes: 0

radar
radar

Reputation: 13425

You need to translate into SQL in the similar way

AND ( 
ISNULL(@type, ' ') = ' '
OR ( @type='IL' AND ea.lunchtype = 'C' )
OR ( @type='EL' AND ea.lunchtype = 'CI' )
OR (@type='L' AND ea.islunch = 1   )
OR (@type='N' AND ea.islunch = 0    )
OR (@type='R'  AND (ea.ismanual = 1 AND ea.lunchtype = 'K')
)
)

Upvotes: 1

Related Questions