How can I raise a custom exception in SQL Server

Question

I have a stored procedure spMyProc. The procedure is passed a table name as a parameter @TableName. It is important to verify that the table exists before proceeding to the body of the procedure.

How can I create a custom exception to raise an error if the table name is invalid?

I am aware of TRY and CATCH but I'm unsure how to put this together with custom exceptions.

Regards.

Answer 1

Look into RAISERROR() funiction.

TRY.. CATCH works pretty same as it would work in any other programming language

BEGIN TRY

  -- do your checks 
      IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE NAME = @TableName)
       BEGIN
           RAISERROR('Table does not exist', 16,1)
       END

  -- rest of the code if  checks are passed 
  -- if above checks are not passed and you riase an error 
  -- control will skip any code in TRY Block after the error has been 
  -- Raised and staright jump to Catch block.

END TRY

BEGIN CATCH 
  -- Do your error logging 
  -- Other stuff 
  -- you have access to ERROR_ functions here to get detailed info about errors
END CATCH 

Answer 2

I think one of examples supplied here RAISERROR should match your problem. New versions of SQL Server will use THROW instead of RAISERROR.