UDF vs CHECK Constraint

As you probably know there are more accurate/efficient ways to create a CHECK constrain without using UDF. However, there under some circumstances we have to.


CREATE TABLE Test (col VARCHAR(20))
GO

This UDF returns 0 when string is an empty or NULL

CREATE FUNCTION dbo.fn_check_null_or_blank
(
@StringToCheck VARCHAR(250)
)
RETURNS INT
AS
Begin
RETURN 1-SIGN(LEN(COALESCE(@StringToCheck,'')))
End

ALTER TABLE Test
ADD CONSTRAINT df_col CHECK (dbo.fn_check_null_or_blank(col)=0)

Usage

INSERT INTO Test VALUES (NULL)--Failed
INSERT INTO Test VALUES ('Hello World')--Works
INSERT INTO Test VALUES (' ')--Failed


DROP TABLE Test
DROP FUNCTION dbo.fn_check_null_or_blank

In the above example I wanted just to show you how you can define a CHECK constraint by using UDF.