I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel record for which there exists a record in the auth table with their personnel number and the authType of 8 (which would indicate they are authorized to sign reports). My UDF should check for the existence of such a auth record and return a bit (0 if it does not exist, 1 if it does). I'm working on SQL Server Express Edition.
I've pretty much re-created my situation at SQLFiddle:
CREATE TABLE personnel( personnel INT IDENTITY(1, 1) NOT NULL, firstName VARCHAR(20), lastName VARCHAR(20), login VARCHAR(20) DEFAULT NULL, title varchar(20) DEFAULT NULL, initials varchar(4) NOT NULL, startDate DATE DEFAULT GETDATE(), CONSTRAINT PkPersonnel PRIMARY KEY(personnel), CONSTRAINT UqPersonnelFirstNameLastName UNIQUE(firstName, lastName), CONSTRAINT UqPersonnelInitials UNIQUE(initials) ); CREATE TABLE authType( authType INT NOT NULL IDENTITY(1, 1), authName varchar(50) CONSTRAINT PkAuthTypeAuthType PRIMARY KEY(authType) ); CREATE TABLE auth( auth INT NOT NULL IDENTITY(1, 1), personnel INT NOT NULL, authtype INT NOT NULL, date date DEFAULT GETDATE() CONSTRAINT PkAuthAuth PRIMARY KEY(auth) CONSTRAINT FkAuthAuthType FOREIGN KEY(authtype) REFERENCES authtype(authtype) ); CREATE FUNCTION checkIfAuthorized (@personnel INTEGER, @authType INTEGER) RETURNS Bit AS BEGIN Return Case When Exists (Select 1 FROM auth Where personnel = @personnel AND authtype = @authtype) Then 1 Else 0 End END GO CREATE TABLE report( report INTEGER NOT NULL IDENTITY(1, 1), iteminJob INTEGER NOT NULL, reportDate DATE NOT NULL, notes VARCHAR(200), signedBy INTEGER NOT NULL, reviewedBy INTEGER, conformance VARCHAR(30) CONSTRAINT PkReport PRIMARY KEY(report), CONSTRAINT [Reports must be associated with an item in a job.] FOREIGN KEY(iteminJob) REFERENCES iteminjob(iteminJob), CONSTRAINT [Must be signed by an authorized signatory.] CHECK(metrologyTesting.dbo.checkIfAuthorized(signedBy, 8)) );
Whenever creating my report table on SQL Server Express, the error message is:
'checkIfAuthorized' is not a recognized built-in function name.
even though in SSMS's Object Explorer, it's clearly there under [MyDatabase]>Programmability>Functions>Scalar-valued Functions
The results on SQLFiddle:
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
...are even more perplexing to me. The UDF is set up to return a bit.
Please refrain from recommending I use triggers instead.