SQL Case Syntax Error

Question

What is wrong with this syntax?

Declare @comparisongroup int;
...
Insert Into @universitytemp Case @comparisongroup
    When -1 Then Select * From dbo.University;
    When -2 Then Select * From dbo.University Where SubDivisionName = @group; 
    When -3 Then Select * From dbo.University Where ConferenceName = @group; 
    Else Select * From dbo.GroupUniversity Where GroupID = CONVERT(int, @group);
End;

This doesn't work either

Declare @comparisongroup int;
...
Case @comparisongroup
    When -1 Then Insert Into @universitytemp Select * From dbo.University
    When -2 Then Insert Into @universitytemp Select * From dbo.University Where SubDivisionName = @group 
    When -3 Then Insert Into @universitytemp Select * From dbo.University Where ConferenceName = @group 
    Else Insert Into @universitytemp Select * From dbo.GroupUniversity Where GroupID = CONVERT(int, @group)
End;

Neither does this:

Declare @comparisongroup int;
...
Case
    When @comparisongroup = -1 Then Insert Into @universitytemp Select * From dbo.University;
    When @comparisongroup = -2 Then Insert Into @universitytemp Select * From dbo.University Where SubDivisionName = @group
    When @comparisongroup = -3 Then Insert Into @universitytemp Select * From dbo.University Where ConferenceName = @group 
    Else Insert Into @universitytemp Select * From dbo.GroupUniversity Where GroupID = CONVERT(int, @group)
End;

I get errors saying Incorrect syntax near the keyword 'When', 'Else', and 'End'

Damien_The_Unbeliever · Accepted Answer · 2012-03-04 06:36:25Z

2

That's because CASE is an expression that returns a value. A single value. Not a result set.

You might try:

SELECT
    * --todo - explicit column list
FROM
   dbo.University u
WHERE
   @comparisongroup = -1 OR
   (@comparisongroup = -2 AND SubDivisionName = @Group) OR
   (@comparisongroup = -3 AND ConferenceName = @Group)
UNION ALL
SELECT
    * --todo - explicit column list
FROM
    dbo.GroupUniversity
WHERE GroupID = CONVERT(int, @group) AND NOT @ComparisonGroup in (-1,-2,-3);

I.e. not use a CASE expression at all. As a stylistic point, I'd also probably separate @group into two parameters/variables - one which has "name" in its name, and continues to be (varchar?), the other of which is explicitly an int and used solely for the GroupID comparison.

answered Mar 4, 2012 at 6:36

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Case Syntax Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related