CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How to show custom error messages in SQL Server

I am trying to show a user defined error message once a query executes. For example I have a table names COA and I am deleting a record from this table whose ID is 200001, I want to display a message at the bottom which in case ID is not present in the table says "ID is not present" or something custom defined.

  DELETE FROM [dbo].[COA]
  WHERE ID = 200001
  IF [dbo].[COA].ID <> 200001
  PRINT N'The current database is master.';

Is this possible or not ?

Upvotes: 0

Views: 1069

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the OUTPUT clause in the DELETE statement in order to store the rows deleted, if any:

DECLARE @MyTableVar TABLE  
(  
    ID INT
);  

DELETE FROM [dbo].[COA]
OUTPUT DELETED.ID INTO @MyTableVar
WHERE ID = 200001

IF EXISTS (SELECT * FROM @MyTableVar)
  PRINT N'The current database is master.';

Note: You can define @MyTableVar to include additional fields from the original table, so that you are able to create a more customized message.

Upvotes: 1

DxTx
DxTx

Reputation: 3357

You can do something like this...

DELETE FROM [dbo].[TableName]
WHERE ID = '1'
IF (@@rowcount < 1 ) 
BEGIN
    PRINT N'ID is not present.'; 
END

Upvotes: 0

Related Questions