CODEWITHSUNDEEP
sqlsql-serverconstraintssql-server-2014
Ramon Fonis
Ramon Fonis

Reputation: 167

Custom Error show with constraints violation SQL server

I am new to SQL Server 2014. I created the table student, I made three columns as below. The melicode has the CHECK constraints and it works well when you want you use Edit top 200 rows. When you try to give the melicode a number less than two, you face a Errorbox; but the problem here: can I print an custom error box to say something, if the constraint is violated, for instance "The melicode range starts from 2".

enter image description here

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

    CREATE TABLE [dbo].[student](
    [stdid] [nchar](10) NOT NULL,
    [stdname] [nvarchar](50) NOT NULL,
    [melicode] [char](10) NOT NULL,
) ON [PRIMARY] 

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[student]  WITH CHECK ADD CHECK  (([melicode]>(2)))

I tried the below code to implement it, but face with error:

IF  melicode < 2
    RAISERROR ('melicode can not be less than 2',0,1)
GO

Upvotes: 1

Views: 536

Answers (1)

knkarthick24
knkarthick24

Reputation: 3216

You can add Custom Error Msg error in Sql Server as below:
Note: MsgNum should be greater than 50000

exec sp_addmessage @msgnum=50009,@severity=1,@msgtext='melicode can not be less than 2'

Message created can be found here:

 SELECT * FROM SYS.messages

Your Code should goes like below:

BEGIN TRY

UPDATE STATEMENT GOES HERE  
OR  
INSERT STATEMENT GOES HERE

END TRY

BEGIN CATCH

RAISERROR(50009,1,1)

END CATCH

Upvotes: 2

Related Questions