Function Based Check Constraint

Question

For an class assignment, we have the following scenario:

"We are seeing some of our staff reserve event venues that cannot accommodate the expected attendance at the event. Nothing worse than having 500 people attend an event that has a max capacity of only 200. We need to add a constraint that will prevent this from happening. To test your constraint add the following 2 events."

We are directed to use a function and a check constraint that utilizes the output of that function. This has been my best shot at it so far:

CREATE FUNCTION f_nfp_venues_max_capacity
(
  @event_exp_attendance int,
  @event_venue_id int
)    
RETURNS bit
AS
BEGIN
DECLARE @ResultBit BIT = 1
IF @event_exp_attendance > (SELECT venue_max_capacity FROM nfp_venues WHERE @event_venue_id = venue_id)
  SELECT @ResultBit = 0
RETURN      @ResultBit

END


ALTER TABLE nfp_events
  ADD
    CONSTRAINT ck_event_venue_capacity CHECK
       (([dbo].[f_nfp_venues_max_capacity] (event_exp_attendance)=(1)))

I get this error when I try to execute the constraint:

Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.f_nfp_venues_max_capacity.

Any help would be greatly appreciated.

[on the same course as this guy?](http://stackoverflow.com/q/26693571/73226). The check constraint method is very brittle. It won't fire if the venue capacity is changed in the venues table for example. So potentially allowing data to exist that violates the rule. — Martin Smith, Nov 02 '14 at 21:41
@MartinSmith Wouldn't disallowing lowering venue capacity if there are events that would violate the change require a check on the venue tables for _venue capacity >= existing event capacity_? Or could that be handled by the indexed view you used? — jpw, Nov 02 '14 at 21:46
@jpw the indexed view would handle both sides automatically. You could create another check constraint on then venues table but there's still probably other ways it could fail. — Martin Smith, Nov 02 '14 at 21:57
@MartinSmith Good to know. Your approach on the other question did seem solid and was new to me; always good to learn new tricks. — jpw, Nov 02 '14 at 21:58

score 1 · Accepted Answer · answered Nov 02 '14 at 21:21

You're missing one required argument when you call the function in the constraint. You need to pass the event_venue_id too in the constraint:

[dbo].[f_nfp_venues_max_capacity] (event_exp_attendance, event_venue_id)

just as the error message suggests.

Function Based Check Constraint

1 Answers1