Error handling in Views - 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 handling in Views

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-20 : 19:37:40

Is there any way we can define error handlings in views.So that when I call or refresh the view.If it gives an error then i should be able to capture the error.

Kristen
Test

22859 Posts

Posted - 2005-08-21 : 02:17:09

What sort of thing what you want to "catch" as an error?

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-21 : 13:50:32

Any type of data error...

Kristen
Test

22859 Posts

Posted - 2005-08-21 : 14:55:54

"Any type of data error..."

I can't visualise what you mean - can you provide an example?

The VIEW will just be a SELECT - I can't see that there is an error that you can catch - if you get a DeadLock or somesuch that's outside the scope of the SELECT. If you have, say, an age column and it has a value of "-1" then you can't raise an error on that either. So I haven;t understood what you are trying to catch.

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-22 : 21:00:46

Let me explain a case where I have to capture the error

create table table1
(
inc int,
name varchar(100)
)

insert into table1
values(1,'Test1')

insert into table1
values(2,'Test2')

CREATE VIEW VIEW1
AS
SELECT inc, name
FROM table1

----------------------------------
CREATE PROCEDURE testme

@error_id int output

AS

select * from view1

set @error_id=@@error

IF @error_id<> 0
BEGIN
set @error_id=1
END
ELSE
BEGIN
set @error_id=0
END
-------------------------------------

select * from table1
select * from view1

-------------------

Now these 2 above statements give me results

-- Basic aim is to get the error id

declare @test int
execute testme @test output
PRINT @test

Now I dropped the table
drop table table1

and when I run the above code

declare @test int
execute testme @test output
PRINT @test

Iam not getting the error id.

Please help me out

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-22 : 21:21:39

maybe you could put an IF EXISTS in your sproc testing for the existence of the object before you select from it.

A view is just a stored select statement. Nothing more. You cannot put any error handling in it, or branching logic, other than what you get from CASE, or COALESCE and ISNULL type functions.

-ec

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-08-22 : 21:37:45

I changed my stored procedure to

CREATE PROCEDURE testme

@error_id int output

AS

IF exists(select * from view1)
BEGIN
select @error_id=@@error
END
ELSE
BEGIN
set @error_id=@@error
END
GO

But when i drop table1 then
Iam not able to get the error number ....Its giving me an error of

Server: Msg 208, Level 16, State 1, Procedure VIEW1, Line 3
Invalid object name 'data_migration.table1'.
Server: Msg 4413, Level 16, State 1, Procedure testme, Line 9
Could not use view or function 'view1' because of binding errors.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-22 : 22:31:49

I meant to use the IF EXISTS to determine if that view exists in your database. Do a test against the INFORMATION_SCHEMA views to determine if you really have a view called view1 and then select from it if you do. If you don't have that view, then return an error and have your calling procedure handle that.

SQL Server error handling is a little different and pretty incomplete. THe fact that you are attempting to select from an object is going to override whatever error you have assigned in your sproc. From what I have seen of SQL2K5, many of these issues are handled as there are many new error handling features.

Anyway, the situation you outlined is better handled using the IF EXISTS methodology then trying to manipulate the @@error variables.

-ec

Kristen
Test

22859 Posts

Posted - 2005-08-23 : 03:16:57

IF EXISTS is only going to catch one type of error though ... whereas I presume sqllearner would like to catch DeadLock and bunch of other possible errors.

sqllearner: Can you handle it client side, i.e. if there is an error in the ADO Errors collection?

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:37:13

quote:
Originally posted by Kristen

IF EXISTS is only going to catch one type of error though ... whereas I presume sqllearner would like to catch DeadLock and bunch of other possible errors.

sqllearner: Can you handle it client side, i.e. if there is an error in the ADO Errors collection?

Kristen

I guess I don't really understand exactly what he is trying to do then.

I try to avoid situations in TSQL where there might be errors because the error handling is not so great. You are much better off even with VBscript then you are with TSQL.

Anyway, maybe SQLearner can tell us exaclty what error he is trying to catch.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources