Sql-server – Incorrect syntax near ‘)’ when executing multiple select statement

sql server

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  )

Best Answer

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.

Related Question