0

I want to use a CASE statement instead of an IF...ELSE statement but the CASE statement give me issues. Please check the stored procedure below. It is complaining about a syntax error next to UPDATE.

ALTER PROCEDURE [dbo].[UpdateRFQStatusDashboardCountForAllUsers]
(
    @UserID [nvarchar](128)='6aab7553-72ef-46d8-b831-5c8d5e269cb3',
    @ColumnName  [nvarchar](50)='Close'
)
AS
BEGIN
SELECT
  CASE @ColumnName
      WHEN 'New' THEN
       UPDATE [dbo].[RFQStatusDashboardCountForAllUsers]
       SET New = New + 1
       WHERE [UserID] = @UserID
      
      WHEN 'Close' THEN
        UPDATE [dbo].[RFQStatusDashboardCountForAllUsers]
       SET [Open] = [Open] - 1, Closed = Closed + 1
       WHERE [UserID] = @UserID;
      
  END CASE

END
Thom A
  • 88,727
  • 11
  • 45
  • 75
Ntando
  • 29
  • 1
  • 7
  • 2
    `CASE` is an **expression** that returns a *scalar value*, it is *not* a logical flow operator (such as a `switch` statement in C#). It doesn't work like the above. Use `IF...ELSE` logical flow operators. – Thom A Apr 18 '23 at 11:19
  • Also, as a side note, your parameter `@UserID` looks like it will contain a `uniqueidentifier`, so why do you define it as an `nvarchar(128)`? – Thom A Apr 18 '23 at 11:20
  • The system am busy maintaining the uniqueidentifier was made a nvarchar(128) – Ntando Apr 19 '23 at 12:04

0 Answers0