CODEWITHSUNDEEP
sql-serverdatabaseconstraints
Prahlad
Prahlad

Reputation: 13

adding a constraint that checks existence of stored proc

I am using SQL Server 2005 and in one of the tables i have a column that stores stored proc name. While inserting values in that column I want to ensure that a stored proc of that name exists in the database.

ALTER TABLE MyTable WITH CHECK 
   ADD CONSTRAINT [CK_MyTable_MyColumn] CHECK ((SELECT COUNT(*)  FROM sys.sysobjects WHERE id = object_id(MyColumn) AND OBJECTPROPERTY(id, N'IsProcedure')=1) = 1)

but this gives the following error

Subqueries are not allowed in this context. Only scalar expressions are allowed.

How can I do this.

Upvotes: 1

Views: 398

Answers (2)

Cade Roux
Cade Roux

Reputation: 89671

Note that this type of constraint is only guaranteed to be true at the time a record is inserted or updated. The proc can be dropped without consequence. You may need to add a DDL trigger to catch things which get dropped which have references in the table and/or an exception reporting procedure which runs on a regular basis to monitor your system's health to ensure any procedure in the table actually exists.

Upvotes: 2

Manfred Sorg
Manfred Sorg

Reputation: 1890

Functions are allowed:

create function dbo.IsProcedure(@ProcName sysname) returns bit as
return (SELECT COUNT(*) FROM sys.sysobjects 
  where id = object_id(MyColumn) and OBJECTPROPERTY(id, N'IsProcedure')=1);
go

ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT [CK_MyTable_MyColumn]
CHECK (dbo.IsProcedure(MyColumn) = 1);

Upvotes: 4

Related Questions