CODEWITHSUNDEEP
sqlsql-server
k dud
k dud

Reputation: 13

Incorrect syntax near ')'

How to fix the syntax, I wanted to execute only one select statement based on user input ie @id

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)

Upvotes: 1

Views: 188

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Presumably, you want if:

Create Procedure employee (
    @id int,
    @year int
)
AS
BEGIN
    IF @id = 1
    BEGIN
        select a.name, b.roles
        from employee a 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 join
             department b
             ON a.id = b.id
        where b.years IN (@year) OR b.dayoff IS NOT NULL  ) ;
    END
END;

I am not sure why you are using in with @year; this will only work if there is one value in @year.

Upvotes: 1

Rahul
Rahul

Reputation: 77936

Change the below line

 SELECT CASE WHEN @id = 1 THEN

To this

IF @id = 1 THEN

Cause you can't use CASE expression like this and it should be used within a query. With that your code block should look like

IF @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 

(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  )

Upvotes: 0

Related Questions