CODEWITHSUNDEEP
sqlt-sql
Anthony Herbert
Anthony Herbert

Reputation: 5

Function Based Check Constraint

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.

Upvotes: 0

Views: 146

Answers (1)

jpw
jpw

Reputation: 44891

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.

Upvotes: 1

Related Questions