SQL Server Unable to create procedure with case expression in where clause

63lcw9qa  于 2023-02-18  发布在  其他
关注(0)|答案(2)|浏览(92)

Considering this table:

CREATE TABLE RACKRATE 
(
    RackRateID SMALLINT PRIMARY KEY IDENTITY,
    RoomTypeID SMALLINT REFERENCES ROOMTYPE(RoomTypeID) NOT NULL,
    HotelID SMALLINT REFERENCES HOTEL(HotelID) NOT NULL,
    RackRate SMALLMONEY NOT NULL,
    RackRateBegin DATE NOT NULL,
    RackRateEnd DATE NOT NULL,
    RackRateDescription VARCHAR(200)
);

I'm trying to create a stored procedure to update the table, like so:

CREATE PROCEDURE usp_UpdateRackRates
    @hotelid smallint,
    @roomtypeid smallint = NULL,
    @rackratedescription varchar(200) = NULL,
    @percent decimal(6,4)
AS
    UPDATE RACKRATE 
    SET RackRate = CASE 
                       WHEN @percent >= 0 
                           THEN CEILING(RackRate * ((100 + @percent)/100))
                       ELSE FLOOR(RackRate * ((100 + @percent)/100))
                   END
    WHERE 
        (CASE
             WHEN @roomtypeid IS NOT NULL AND @rackratedescription IS NOT NULL
                 THEN HotelID = @hotelid AND RackRateDescription = @rackratedescription
             WHEN @hotelid IS NOT NULL 
                 THEN HotelID = @hotelid
             WHEN @rackratedescription IS NOT NULL 
                 THEN RackRateDescription = @RACKRATEDESCRIPTION
         END);

When I run this in SQL Server, I get this error

Incorrect syntax near '='

pointing at the first = sign in the where clause.

As a sanity check, I created the same table in sqlite and checked this same update statement with it, and sqlite worked just fine (obviously just the update statement, sqlite doesn't have stored procedures).

Where am I going wrong here?

z31licg0

z31licg01#

SQL Server does not really have a boolean type. (Or more properly, SQL Server treats Boolean in a separate category from other types, with limited usages.) The BIT type is close, but not equivalent.

(See SQLpro's note below regarding ISO SQL standard compliance.)

The following is a direct adaptation of your logic that takes the results of each case condition and emits a 1 or 0. The WHERE clause then checks for an overall result = 1. I also added an ELSE 1 to handle the "nothing to check" case.

WHERE 1 =
        (CASE
             WHEN @roomtypeid IS NOT NULL AND @rackratedescription IS NOT NULL
                 THEN CASE WHEN HotelID = @hotelid AND RackRateDescription = @rackratedescription
                      THEN 1 ELSE 0 END
             WHEN @hotelid IS NOT NULL 
                 THEN CASE WHEN HotelID = @hotelid
                      THEN 1 ELSE 0 END
             WHEN @rackratedescription IS NOT NULL 
                 THEN CASE WHEN RackRateDescription = @RACKRATEDESCRIPTION
                      THEN 1 ELSE 0 END
             ELSE 1
         END);

There may be ways to reduce this, but I believe the current form reflects your intent.

Side note: Be aware that the above approach may lead to performance issues. The SQL Server query optimizer will not be able to effectively use an index to identify rows to be updated, so a table scan is likely to occur with every execution (or at best a complex index scan).

A better approach that would allow index optimization would be to split the UPDATE into three separate statements, contained within an IF-ELSE structure, and with each having a simplified WHERE condition.

Side note 2: Did you indend to check for @roomtypeid IS NOT NULL and then apply a HotelID = @hotelid condition in the first case branch?

q3qa4bjr

q3qa4bjr2#

CREATE PROCEDURE usp_UpdateRackRates
@hotelid smallint,
@roomtypeid smallint = null,
@rackratedescription varchar(200) = null,
@percent decimal(6,4)
AS
UPDATE RACKRATE SET RackRate = 
CASE WHEN @percent >= 0 THEN CEILING(RackRate * ((100 + @percent)/100))
ELSE FLOOR(RackRate * ((100 + @percent)/100))
  END
WHERE (CASE
  WHEN @roomtypeid IS NOT NULL AND @rackratedescription IS NOT NULL
 THEN HotelID = @hotelid AND RackRateDescription = @rackratedescription
WHEN @hotelid IS NOT NULL THEN HotelID = @hotelid
WHEN @rackratedescription IS NOT NULL THEN RackRateDescription = @rackratedescription

END);

相关问题