How to throw SQLException in stored procedure

Question

How can I throw exception in a stored procedure For example:

@temp int =0

As 
BEGIN
SELECT @int = COUNT(*) FROM TABLE1
END

IF(@temp>0)
throw SQL Exception

P/S: not use return value

mysql or mssql server or both? – Preet Sangha Aug 11 '11 at 12:59 — Preet Sangha, Aug 11 '11 at 12:59

score 18 · Accepted Answer · edited Apr 13 '21 at 06:54

18

RAISERROR for MSSQL Server. As @Marc Gravell: note the severity must be >= 16 for it to surface as a SqlException.

Read this SO answer for MySQL.

This blog post also shows how to do it in MySQL (if <=6.0)

edited Apr 13 '21 at 06:54

Pang

9,073
146
84
117

answered Aug 11 '11 at 13:00

Sunny Milenov

21,386
5
78
105

14

note the severity must be >= 16 for it to surface as a `SqlException` – Marc Gravell Aug 11 '11 at 13:01
@MarcGravell - I did not understood `severity >=16`? – xameeramir Jun 02 '15 at 06:18
1

@zameeramir sql server has the concept of severity levels; only 16 and above are surfaced as exceptions... – Marc Gravell Jun 02 '15 at 07:16

score 1 · Answer 2 · answered Aug 11 '11 at 13:37

In MySQL there is no way to throw an exception in a stored procedure, but you can force an error by selecting from a non-existing table.
It helps if the tablename gives a description of the error.

Example:

DELIMITER $$

CREATE PROCEDURE throw_exception (IN param1 INTEGER)
BEGIN
  DECLARE testvar INTEGER;
  SELECT testfield INTO testvar FROM atable WHERE id = param1 LIMIT 1;
  IF testfield IS NULL THEN
     /*throw the error here*/
     SELECT * FROM 
       error_testfield_in_atable_not_found_youve_entred_a_nonexisting_id_in_throw_exception;
  END IF;
END $$

DELIMITER ;

How to throw SQLException in stored procedure

2 Answers2