WORKAROUND
To work around this problem, you can do one of the following:
- Handle the errors at the client
application.
- Handle the errors outside the Transact-SQL batch or the
stored procedure.
If you want to handle the errors in the Transact-SQL batch
or the stored procedure, handle the problem that causes the error before
you run the Transact-SQL statement.
For example, if you are
referring to a table that does not exist in the database, check whether
the table exists or not. If the table does not exist you can then display
a user-defined message. Here is an example:
create procedure TestProcedure @id int
as
BEGIN
Declare @price int
SELECT @price = price
FROM Books
WHERE bookid = @id -- Refer to a table that does not exist
IF @@error <> 0
Begin
print 'Error Occurred.'
End
END
go
Exec TestProcedure 10
go
When you run the procedure, you receive the following error
message:
Server: Msg 208, Level 16, State 1,
Procedure test, Line 4
Invalid object name 'Books'.
To
handle the error in a stored procedure, you must check whether the table exists
or not. For example:
create procedure TestProcedure1 @id int
as
Declare @price int
Declare @count int
BEGIN
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) - See if table exists
Begin
SELECT @price = price
FROM Books
WHERE bookid = @id
IF @@error <> 0
Begin
print 'Error occurred.'
End
End
Else
Begin
print 'Table does not exist.'
End
END
go
Exec TestProcedure1 10
go