Stored Proc with DataTable Parameter - How to perform conditional update without looping through the datatable

Question

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?

You need to provide minimal reproducible example with sample data and desired results so we can understand the purpose of your code — Dale K
– Dale K, Commented Apr 18, 2024 at 8:45
why bother, if NumberOfPlacesAvailable - NumberOfPlacesBooked =0 then isfullybooked. — P.Salmon
– P.Salmon, Commented Apr 18, 2024 at 8:58
As a rule of thumb, you really shouldn't store values that can easily be computed - so the IsFullyBooked value shouldn't even be stored in the database in the first place, when you can easily compute it using a simple query. — Zohar Peled
– Zohar Peled, Commented Apr 18, 2024 at 9:42
The reason for the isFullyBooked flag is so that when looking up events (in their thousands), there is no need to perform the calculation for each lookup. I appcreciate it's not required, and it's just a preferred way for me to do it. — MRB
– MRB, Commented Apr 18, 2024 at 9:44
The main problem with things like that is that they tend to go out of sync and you might get wrong results and not even know it. — Zohar Peled
– Zohar Peled, Commented Apr 18, 2024 at 9:54

Zohar Peled · Accepted Answer · 2024-04-18 10:38:45Z

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;

P.Salmon · Accepted Answer · 2024-04-18 09:53:46Z

2

Just do it as you go. For example

create table t (id int,NumberOfPlacesAvailable int, NumberOfPlacesBooked int, isfullybooked int);

insert into t values (1,10,5,null),(2,10,10,null);

update t 
set NumberOfPlacesBooked = NumberOfPlacesBooked + 5,
    isfullybooked = case when NumberOfPlacesAvailable - (NumberOfPlacesBooked +5) = 0 then 1 else 0 end
where id = 1

select * from t

id          NumberOfPlacesAvailable NumberOfPlacesBooked isfullybooked
----------- ----------------------- -------------------- -------------
1           10                      10                   1
2           10                      10                   NULL

answered Apr 18, 2024 at 9:53

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

2 Comments

Zohar Peled Over a year ago

This is still not safe. What if someone writes a new stored procedure, or even update the values directly via SSMS? Computing columns should never* be done by SQL code (* of course, for every rule there's an exception, probably for this one as well)

MRB Over a year ago

Thanks for taking the time to provide that answer, it's actually exaclty what I needed when adapted to use the datatable. It's I was just about to mark as the answer (and techincally it is) however after reading the answer from Zohad it gives me a better end result, in my case. I really appreciate you taking the time , I learned something from this.

Collectives™ on Stack Overflow

Stored Proc with DataTable Parameter - How to perform conditional update without looping through the datatable

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related