CODEWITHSUNDEEP
sql-serverif-statementcase
Chris
Chris

Reputation: 444

Case Statement Syntax - SQL Server

I'm trying to integrate a really simple check into a trigger with SQL Server 2012. During an INSTEAD OF trigger, I would like to check the following as per this pseudo code:

IF Inserted.FloorRef = ('various', 'different', 'strings')
THEN check that Inserted.FloorNumber IS NOT NULL

I've declared a bit variable that gets set to 1 if this test is passed or if the else condition is met, so that further code can be run under known conditions. My code is below, but it fails with: Incorrect syntax near the keyword 'CASE' (Inserted.FloorRef) And also; Must declare the scalar variable "@Passed"

There must be a syntax error or ten, but I'm failing to find out where, what have I got wrong?

DECLARE @Passed BIT = 0

CASE Inserted.FloorRef 
    WHEN EXISTS IN ('Floor', 'Level', 'Fl', 'L') THEN --FloorNumber is required
        CASE Inserted.FloorNumber 
            WHEN IS NOT NULL THEN --FloorNumber is given
                @Passed = 1
            ELSE --FloorNumber is required but is missing
                @Passed = 0
        END;
    ELSE --FloorNumber is not required
        @Passed = 1
END;

IF @Passed=1
    BEGIN

EDIT: The solution just needed a minor correction to Daniel E's, proposed solution below:

SELECT *
    ,CASE WHEN Inserted.FloorRef IN ('Floor', 'Level', 'Fl', 'L') THEN --FloorNumber is required
        CASE
            WHEN Inserted.FloorNumber IS NOT NULL THEN 1 --FloorNumber is given
            ELSE 0 --FloorNumber is required but is missing
        END
    ELSE 1 --FloorNumber is not required
    END [Passed]
FROM Inserted

Upvotes: 0

Views: 118

Answers (3)

Daniel E.
Daniel E.

Reputation: 2059

This will tell you each row pass/fail, and you can use those results as you wish.

SELECT * 
    ,CASE WHEN Inserted.FloorRef  IN ('Floor', 'Level', 'Fl', 'L') THEN --FloorNumber is required
             CASE WHEN Inserted.FloorNumber IS NOT NULL THEN 1 --FloorNumber is given
               ELSE 0 --FloorNumber is required but is missing                    
               END
     ELSE 1--FloorNumber is not required
     END [Passed]
FROM Inserted

Upvotes: 2

StuartLC
StuartLC

Reputation: 107237

As others have stated, note that the INSERTED and DELETED pseudo tables in triggers can have multiple rows when multiple rows are inserted / updated / deleted, which you will need to handle.

That said, this will examine the first inserted row and set the @Passed bit.

  DECLARE @FloorRef VARCHAR(20);
  DECLARE @FloorNumber  VARCHAR(20);
  DECLARE @Passed BIT = 0;

  SELECT TOP 1 @FloorRef = Inserted.FloorRef, @FloorNumber = Inserted.FloorNumber
  FROM INSERTED;

  SELECT @PASSED = CASE 
      WHEN @FloorRef IN ('Floor', 'Level', 'Fl', 'L') THEN --FloorNumber is required
          CASE
              WHEN @FloorNumber IS NOT NULL THEN --FloorNumber is given
                  1
              ELSE --FloorNumber is required but is missing
                  0
          END
       ELSE --FloorNumber is not required
          1
       END;

SqlFiddle here

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Inserted is a table you have to treat it like one. There is not from clause. You also have to understand that there may be multiple records in inserted so you can't set a scalar variable, what happens if some records should be passed = 1 and others should be 0. Triggers do not work row-by-row!

I woudl alos as Damien suggested look at a check constraint instead.

Upvotes: 1

Related Questions