0
Incorrect syntax near ')' 

How do I execute only one select statement based on user input i.e @id. Here is my syntax.

Create Procedure employee
@id int,
@year int
AS

SELECT CASE WHEN @id = 1 THEN

(select a.name, b.roles from employee a 
INNER JOIN department b ON a.id = b.id
where b.years IN (@year) 
OR b.roles IS NOT NULL  ) 

ELSE CASE WHEN @id = 2 THEN 

(select a.name, b.dayoff from employee a 
INNER JOIN department b ON a.id = b.id
where b.years IN (@year) 
OR b.dayoff IS NOT NULL  ) 
5

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
ELSE
  -- 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 CASE here:

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
    @id   int, 
    @year int
AS
BEGIN
  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;
END
GO

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.

3

You can use an IF statement to execute one or the other.

Create Procedure employee
    @id int,
    @year int
    AS

IF @id = 1
BEGIN
select a.name, b.roles from employee a 
INNER JOIN department b ON a.id = b.id
where b.years IN (@year) 
OR b.roles IS NOT NULL;
END
ELSE IF @id = 2
BEGIN
    select a.name, b.dayoff from employee a 
INNER JOIN department b ON a.id = b.id
where b.years IN (@year) 
OR b.dayoff IS NOT NULL;
END 

GO

For completeness you should have an else clause that will provide a return or throw an error when the value of @id isn't 1 or 2.

  • This might be okay for simple statements like this, but that's a lot of repeated code if the queries are more complex. – Aaron Bertrand Jan 22 '16 at 3:33
  • 1
    @AaronBertrand completely agree. My answer becomes unmanageable if you have more then a handful of values for @id. For me personally if I'm returning a different set of columns based on a value of a parameter I would opt to make them separate procs to keep things isolated and require the business layer to determine which one to call. That adds more procs to the database but it keeps the logic in the procs simpler. – Aaron Jan 22 '16 at 3:58
2

My understanding is that CASE simply returns a single value based on conditions. It doesn't provide flow control like a procedural language.

Please consider combining SQL and selecting different fields within that SQL :

select 
    a.name, 
    case
      when @id=1 then b.roles 
      when @id=2 then b.dayoff
    end  as b.something        
from employee a 
     INNER JOIN department b ON a.id = b.id    
where 
     b.years IN (@year) 
     OR case 
          when @id=1 then b.roles
          when @id=2 then b.dayoff 
        end IS NOT NULL ;

You can't conditionally rename an output column, so pick some generic name for that work for both conditions. Second I would clearly define 'else' conditions,e.g. when @id is neither 1 or 2, which is possible an error state, but needs to be accounted for. Your other choice is to call different SQL based on application logic.

  • 2
    Keep in mind that each possible output of a CASE expression must have compatible types, so if roles and dayoff are not compatible (say one is a string and one is a bit), this will be problematic. – Aaron Bertrand Jan 22 '16 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.