Customizable CHECK Error Message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Customizable CHECK Error Message

Author

Topic

soulblazer
Starting Member

2 Posts

Posted - 2014-10-13 : 14:33:11

Hello, I have the following problem:

I have a table named "Gender", which only must accepts values "Female" (ID = 1) and "Male" (ID = 2). Currently, I'm using a CHECK CONSTRAINT and a FUNCTION:

CREATE FUNCTION [dbo].[COUNT_ROWS_TABLE](@TABLE_NAME VARCHAR(50))
RETURNS BIGINT
AS
BEGIN
DECLARE @ROW_COUNT BIGINT
SELECT @ROW_COUNT = SUM(B.ROWS) FROM sys.tables A
INNER JOIN sys.partitions B ON B.OBJECT_ID = A.OBJECT_ID
WHERE A.is_ms_shipped = 0 AND B.index_id IN (1,0)
AND A.name = @TABLE_NAME
GROUP BY A.name
RETURN @ROW_COUNT
END
GO

ALTER TABLE [dbo].[Gender] WITH CHECK ADD CONSTRAINT [CK_GENDER]
CHECK (([ID_genero]=[dbo].[COUNT_ROWS_TABLE]('Gender') AND
[ID_gender]<=(2) AND [ID_gender]=(1) AND [des_gender]='Female' OR [ID_gender]=(2) AND [des_gender]='Male'))
GO
ALTER TABLE [dbo].[Gender] CHECK CONSTRAINT [CK_GENDER]
GO

So, during insert, ID_gender must be consecutive and if it's 1, des_gender must be "Female" or if it's 2, des_gender must be "Male".
What I want to accomplish it's either:
A) Having MY OWN CHECK error message, not the SQL one (seriously, it's annoying and you can take long to understand what went wrong).
B) Not use CHECKS at all and CREATE a TRIGGER that pops an error message if the previous statements are not true.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 14:55:01

You would do this with TRY/CATCH. In the CATCH is where you'd have your custom message. You could check for the specific error number and send back your custom message if it matches the check constraint error number. Otherwise, send back a generic error message.

So this isn't specific to your check constraint. This is how you handle custom error handling.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources