One important thing to consider:
CASE in T-SQL is an expression that returns a value, not control-of-flow logic like you might find in other languages (e.g. VB treats it like a
switch()). In SQL Server, it can't be used this way; you can't say:
CASE WHEN something THEN
-- run some query
-- run some different query
All output from the
CASE expression must happen at the same scope and within the same query. I've written quite a lengthy article about some of the more common misconceptions about
In your scenario, if these are the only two potential outcomes, then you can probably follow @billspat's advice and just move the location of your
CASE expression into the
WHERE clause of a single query.
If your actual scenario is more complex (like you have 20 or 60 or 7000 different
@id values that can lead to different forms of that
WHERE clause, dynamic SQL might be a better solution (SQL Server will have a very hard time coming up with a single execution plan that is optimal for all variations of the query). Something like this:
CREATE PROCEDURE dbo.employee -- this is an odd name for a procedure
SET NOCOUNT ON; -- should always use this
DECLARE @sql NVARCHAR(MAX) = N'SELECT e.name, d.roles, d.dayoff
FROM dbo.employee AS e -- use logical aliases! What sense do a/b make?
INNER JOIN dbo.department AS d
ON e.id = d.id -- this join condition makes little sense
WHERE d.years IN (@year)';
IF @id = 1 THEN
SET @sql = @sql + N' OR d.roles IS NOT NULL';
IF @id = 2 THEN
SET @sql = @sql + N' OR d.dayoff IS NOT NULL';
EXEC sys.sp_executesql @sql, N'@year int', @year;
I talk about this in a little more detail here:
You could add logic to also dictate which output columns to show, but the application layer should be able to ignore output columns based on the
@id value it passed in.
And please, always use the schema prefix.