i want to handle error if any table is deleted from the database situation is like -
ALTER procedure ABC as
begin tran tansinsert
insert into table1 values (1,2,3)
if @@error <> 0
begin
rollback tran tansinsert
end
else
begin
commit tran tansinsert
end
if i am running this proc and table 'table1 ' was not in database than how i will get error message
One way
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[table1 ]') AND type in (N'U'))
BEGIN
RAISERROR(......)
END
ELSE
BEGIN
--rest of flow
END
GO
You can try this ...
IF OBJECT_ID('DBNAME..TABLENAME') IS NOT NULL
BEGIN
//YOUR CODE FLOW HERE
END
ELSE
BEGIN
//RAISE ERROR HERE
END
GO
Sample code to catch error
BEGIN TRY
EXECUTE Abc
END TRY
BEGIN CATCH
print 'Error got'
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;
If you do the following this does not work.
CREATE PROC test
AS
BEGIN TRY
SELECT * FROM NonexistentTable
END TRY
BEGIN CATCH
-- some code
END CATCH
The only way this works is if you have one stored procedure call another stored procedure such as this:
CREATE PROC Test
AS
SELECT * FROM NonexistentTable
GO
CREATE PROC test2
AS
BEGIN TRY
EXECUTE Test
END TRY
BEGIN CATCH
-- some code
END CATCH
GO
TRY…CATCH constructs do not trap the following conditions:
Warnings or informational messages that have a severity of 10 or lower.
Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
Attentions, such as client-interrupt requests or broken client connections.
When the session is ended by a system administrator by using the KILL statement.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
