I have a stored procedure that takes a DataTable (of int Ids) as a parameter. I perform an update using the datatable. After doing this I would like to add a further conditional update, which may affect some records differently depending on a computed value but don't know how to do this without using a cursor to loop through the Ids in the DataTable. Is it possible to do it without a cursor?
This is the pseduo stored proc code
spEvents_AddRemovePlaces_TVP
@DataTable AS dbo.TVP_IntIds READONLY
,@NumPlaces int
--use the datatable to perform an update to the number of places available for an event
update [events] SET
NumberOfPlacesBooked+=@NumPlaces
WHERE Id in (select Id from @DataTable)
--next check if there are any places left for each record and perform an update (Crucially ONLY if required on a per record basis)
-- at the moment I am using a cursor and doing the following
DECLARE @NumPlacesLeft int
--Loop through the DataTable and perform a check (loop code excluded for clarity)
-- ** START LOOP **
SET @NumPlacesLeft = (select (NumberOfPlacesAvailable - NumberOfPlacesBooked) as totalPlacesLeft from [events] where Id=@EventId )
IF @NumPlacesLeft <=0
-- update db flag to HAS NO places left
update [events] set IsFullyBooked=1 where Id=@EventId
ELSE
-- update db flag to HAS places left
update [events] set IsFullyBooked=0 where Id=@EventId
-- ** END LOOP **
Is there a way to do this without having to loop using a cursor?
What you want to do in such cases is to leverage SQL Server's computed columns.
In your case, this basically means that you'll let SQL Server will compute the IsFullyBooked itself, so you don't have to deal with it at all, and you'll know it's always be up-to-date.
In some cases, you can also make it persistent, meaning the data will actually be saved to the database and only computed when data it depends on is updated.
To do that, you need to go back to your [events] table and alter it like this:
ALTER TABLE [events]
DROP COLUMN IsFullyBooked;
ALTER TABLE [events]
ADD IsFullyBooked as IIF(NumberOfPlacesAvailable-NumberOfPlacesBooked = 0, 1, 0);
This will make SQL Server compute the value of IsFullyBooked whenever you're using it in a SELECT statament (or any DML statement for that metter).
If you want SQL Server to actually store the value and only compute it when either NumberOfPlacesAvailable or NumberOfPlacesBooked is changed, use the keyword PERSISTED when you create the computed column - like this:
ALTER TABLE [events]
ADD IsFullyBooked as IIF(NumberOfPlacesAvailable-NumberOfPlacesBooked = 0, 1, 0) PERSISTED;