Geek asks and answers

Throw an error if the table does not exist in the database

I want to handle the error if any table deleted from the database the situation is similar -

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 run this proc and table 'table1' was not in the database than how I get the error message

+3
source share
3 answers

One of the methods

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
+2
source

Sample code for 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 follow the following, this will 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:

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 capture the following conditions:

  • Alerts or informational messages with a severity of 10 or lower.

  • 20 , SQL Server Database Engine . 20 , , TRY... CATCH .

  • , .

  • KILL.

CATCH, , TRY... CATCH:

  • , , .
  • , , , - .
+1

...

IF OBJECT_ID('DBNAME..TABLENAME') IS NOT NULL
BEGIN
//YOUR CODE FLOW HERE
END

ELSE
BEGIN
//RAISE ERROR HERE
END
GO
+1

More articles:


All Articles