Exceptions - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Exceptions

Author

Topic

7siva7
Starting Member

28 Posts

Posted - 2011-11-27 : 23:43:22

hi all,

i found this Oracle code for exception handling....

CREATE OR REPLACE PROCEDURE My_SP
(
input_var INT,
output_err IN OUT NUMBER
)

IS
vr_errm VARCHAR2(500);
BEGIN
BEGIN
DELETE FROM My_Table
WHERE table_var = input_var;

EXCEPTION

WHEN OTHERS THEN
IF SQLCODE = -02292 THEN
vr_Errm := SQLERRM;
RAISE_APPLICATION_ERROR(-20012,'ERROR_MSG_1',FALSE);
ELSE
vr_Errm := SQLERRM;
RAISE_APPLICATION_ERROR(-20012,'Error_Msg_2 ',FALSE);
END IF;

output_err := 1;

END;

output_err := 0;

COMMIT;

EXCEPTION

WHEN OTHERS THEN
RAISE;
output_err := 2;

END My_SP;

how can we write same code in SQL SERVER?

THanks in advance

HI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-27 : 23:44:12

Take a look at TRY/CATCH in Books Online.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

7siva7
Starting Member

28 Posts

Posted - 2011-11-27 : 23:55:07

but how can i do this ?

vr_Errm := SQLERRM;
RAISE_APPLICATION_ERROR(-20012,'Error_Msg_2 ',FALSE);

with RAISERROR() Method?

HI

7siva7
Starting Member

28 Posts

Posted - 2011-11-27 : 23:56:19

can you give me exact code for the above example ..so that i can learn from your example code....

HI

mimran18
Starting Member

4 Posts

Posted - 2011-11-28 : 00:05:11

Here is the sample code for raising custom error.

Create Procedure test_SP
as
Declare @Count as int

Select @Count=Count(*) from [TableName]

IF @Count>1
Begin
RaisError('Found more than one row',16,1)
Return
End

GO
test_SP

7siva7
Starting Member

28 Posts

Posted - 2011-11-28 : 00:15:05

is there no Exception?

HI

7siva7
Starting Member

28 Posts

Posted - 2011-11-28 : 00:15:45

How can we do this checking

IF SQLCODE = -02292 THEN
vr_Errm := SQLERRM;
RAISE_APPLICATION_ERROR(-20012,'ERROR_MSG_1',FALSE);

HI

mimran18
Starting Member

4 Posts

Posted - 2011-11-28 : 00:36:11

try this.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130340