NO FEATURE OF ERROR HANDLING FOR T-SQL

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

SQL Server Development (2000)

NO FEATURE OF ERROR HANDLING FOR T-SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-09 : 08:26:41

Randhir writes "I have a situation like below, where 'A' is the parameter that get assigned to a numeric variable @intNum , it generates SQL Server Error , how can I trap the error and return back to calling procedure even though I have used @@Error Variable to find out the error it does not work???

DECLARE
@intNum numeric

set @intNum='A'

if @@error<>0
print'Failure'
else
print 'Success'

The Error details :
----------------------------------------------------
Server: Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.
----------------------------------------------------"

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-09 : 08:31:59

Well the 'A' can't be assigned to a numeric variable as it is varchar. Isn't that what the SQL error is telling you?

Raymond

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-09 : 08:34:44

if you're calling this from an application, the application won't have run time error since you already handled the error.

n/a
deleted

35 Posts

Posted - 2004-09-09 : 13:09:13

you need to raise an another error. this will then not be handled by your SQL script and will return it to the application

DECLARE
@intNum numeric

set @intNum='A'

if @@error<>0
begin
print'Failure'
RAISERROR ('bad value type.', 16, 1)
end
else
begin
print 'Success'
end

HTH
Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-09 : 13:14:26

The assignment will abort the batch so you cannot trap the error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-09 : 16:50:55

[code]
DECLARE
@intNum numeric, @check varchar(8000)

SET @check = 'A'
IF ISNUMERIC(@check) = 1
SET @intNum='A'
ELSE
PRINT 'Not Set'

[/code]

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-09 : 16:52:54

there you go, I like Brett's idea.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources