Error Trapping In Procedures - 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 2000 Forums

Transact-SQL (2000)

Error Trapping In Procedures

Author

Topic

ksdevi
Starting Member

2 Posts

Posted - 2003-06-18 : 08:24:50

I have a table which adds and updates information to the table
It has one field
CurrentDate smalldatetime

And i am taking the value as a parameter to stored procedure
And i have implemented error handling using @@Error
And i am using the stored procedure on the asp page
If i enter a date 6/1/2080 which does not come in small date time range it gives

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.

Where as it should have logged it using the mechanism i wrote using @@error

Any Help -----

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 12:53:58

It didn't even make it in to the proc...it failed because the parameter was invalid..





CREATE proc myPROC @x int AS

SELECT @x

IF @@ERROR <> 0 RETURN -1

RETURN 0
go

DECLARE @y int
exec @y = myProc 'a'
SELECT @y

go

drop PROC myPROC
go

-- Try this instead


CREATE proc myPROC @x varchar(256) AS

IF ISDATE(@x) = 1
	RETURN 0
  ELSE
	RETURN -1

go

DECLARE @y int
exec @y = myProc '2000/12/31'
SELECT @y

go

drop PROC myPROC
go

Once you confirm that you have a valid date, then do a convert to smalldatetime and then check @@ERROR...

Brett

8-)

ksdevi
Starting Member

2 Posts

Posted - 2003-06-18 : 23:37:45

The Second method workss but issue i have used lot of procedures in the projects and each procedure might have around 6-10 parameters and i cannot check with a If and it will make the code more long and cumbersom is there an single way which i can check parameters passed can be validated in few lines

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-20 : 14:03:07

SOL

Tell the front end to make sure the data is good before the call...

But....

SOL

There are no Miracles.

EDIT:

quote:

The Second method works

Well of course it does...

Brett

8-)

Edited by - x002548 on 06/20/2003 14:04:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources