Custom function with check constraint SQL Server 2008

by

As stated by Martin Smith using a check constraint with a UDF has some problems and might have a negative impact on performance, but if you want to try it anyway this code should work:

CREATE FUNCTION dbo.CheckVenueCapacity (@venue_id int, @capacity int)
RETURNS int
AS 
BEGIN
  DECLARE @retval int
    SELECT @retval = CASE WHEN venue_max_capacity >= @capacity THEN 0 ELSE 1 END
    FROM venues
    WHERE venue_id = @venue_id 
  RETURN @retval
END;
GO

ALTER TABLE events 
  ADD CONSTRAINT chkVenueCapacity 
  CHECK (dbo.CheckVenueCapacity(event_venue_id, event_expected_attendance) = 0);

More Related Contents:

  1. to select all the employees who have joined most recently under king
  2. How can I delete using INNER JOIN with SQL Server?
  3. Convert varchar into datetime in SQL Server
  4. sql query to return differences between two tables
  5. SQL Joins Vs SQL Subqueries (Performance)?
  6. Check if a row exists, otherwise insert
  7. How to do pagination in SQL Server 2008
  8. formula for computed column based on different table’s column
  9. SQL comments on create table on SQL Server 2008
  10. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  11. How to write UPDATE SQL with Table alias in SQL Server 2008?
  12. SQL Server 2008- Get table constraints
  13. Restrict an SQL Server connection to a specific IP address
  14. How does this CASE expression reach the ELSE clause?
  15. SQL Server: Cannot insert an explicit value into a timestamp column
  16. Execute Dynamic Query with go in sql
  17. is of a type that is invalid for use as a key column in an index
  18. TransactSQL to run another TransactSQL script
  19. Conditional logic in PostDeployment.sql script using SQLCMD
  20. How can I get a list of element names from an XML value in SQL Server
  21. Why are logical reads for windowed aggregate functions so high?
  22. How can I group time by hour or by 10 minutes?
  23. Grant SELECT permission on a view, but not on underlying objects
  24. Using IF ELSE statement based on Count to execute different Insert statements
  25. Restrict varchar() column to specific values?
  26. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  27. Using CASE Statement inside IN Clause
  28. Single or multiple databases
  29. SQL Unpivot multiple columns Data
  30. Foreign Key Not Populating with Primary Key Values

Leave a Comment