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?
2条答案
按热度按时间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.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 anIF-ELSE
structure, and with each having a simplifiedWHERE
condition.Side note 2: Did you indend to check for
@roomtypeid IS NOT NULL
and then apply aHotelID = @hotelid
condition in the first case branch?q3qa4bjr2#
END);