CODEWITHSUNDEEP
sqlsql-serverstored-procedurescase
Tom
Tom

Reputation: 9878

SQL CASE Selection Statement Error

Fairly new to SQL programming.

I'm trying to create a stored procedure which will take the users name, compare it to the users table to retrieve their 'role' and then select all pieces of Feedback from the Feedback table which are related to them.

There are currently 3 possible values: - User - Governance Board - Admin

If the Role is either 'Governance Board' or 'Admin' I want all entries in the table to be retrieved However if the Role is 'User' I only want the entries where the [publish] column is set to true or the 'Author' or 'IdentifiedBy' column is set to themselves.

This is what I currently have however it is throwing errors around the case statement

@Alias varchar(max),
AS

DECLARE @Role varchar(max)

SET @Role =
(
    SELECT [Role] FROM [CSLL].[Users] WHERE [Alias] = @Alias
)

CASE
    WHEN @Role = "Admin" THEN SELECT * FROM [CSLL].[Feedback]
    WHEN @Role = "Governance Board" THEN SELECT * FROM [CSLL].[Feedback]
    WHEN @Role = "User" THEN SELECT * FROM [CSLL].[Feedback] WHERE [Publish] = True OR [Author] = @Alias or [IdentifiedBy]=@Alias
    ELSE
END

Any help would be most appreciated. I'm sure it's just something that I'm doing which is dumb but being new to SQL I can't spot it.

Thanks in advance

Tom

Upvotes: 0

Views: 61

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can write this query as a single select. 

select f.*
from Feedback f cross join
     (SELECT [Role] FROM [CSLL].[Users] WHERE [Alias] = @Alias) r
where (r.[role] in ('Admin', 'Governance Board') ) or
      (r.[role] = 'User' and ([Publish] = True OR [Author] = @Alias or [IdentifiedBy] = @Alias))

I think keeping the logic in one place and having a single query is better from the perspective of undersatnding the logic and maintaining the code.

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

Case statements have to be inside of a select. You can use if instead.

if(@Role = "Admin")
then
    select * from CSLL.Feedback;
else if(...)

Or you can put the case in the where clause:

select *
from CSLL.Feedback
where
CASE
WHEN @Role = "Admin" THEN [IdentifiedBy]
WHEN @Role = "Governance Board" THEN [IdentifiedBy]
WHEN @Role = "User" THEN @Alias
END
IN (IdentifiedBy, Author)
OR [Publish] = True

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

You can't use CASE this way to control flow. You have to use IF.

IF @Role='Admin'
  SELECT * FROM [CSLL].[Feedback]
ELSE IF @Role='Governance Board'
  SELECT * FROM [CSLL].[Feedback]
ELSE IF ... etc

Upvotes: 1

Related Questions