Is there a way to go back to TRY block after error?

I want to check in my stored procedure, if a table contains specific columns. I want to raise an error if such a column exist, because they are not allowed. First I came up with the following idea, which obviously has a defect that I can understand.

  BEGIN TRY 

  SET @sql = N'SELECT TOP 0 forbidden_column_name1 INTO #t1 FROM ' + @input_table ;
  EXEC @err = sp_executesql @sql ;
  IF @err = 0  -- i.e. if succeeds
    RAISERROR('Input table cannot contain ''forbidden_column_name1''. This name is reserved!', 16, 99) ;

  SET @sql = N'SELECT TOP 0 forbidden_column_name2 INTO #t1 FROM ' + @input_table ;
  EXEC @err = sp_executesql @sql ;
  IF @err = 0  -- i.e. if succeeds
    RAISERROR('Input table cannot contain ''forbidden_column_name2''. This name开发者_C百科 is reserved!', 16, 99) ;

  SET @sql = N'SELECT TOP 0 forbidden_column_name3 INTO #t1 FROM ' + @input_table ;
  EXEC @err = sp_executesql @sql ;
  IF @err = 0  -- i.e. if succeeds
    RAISERROR('Input table cannot contain ''forbidden_column_name3''. This name is reserved!', 16, 99) ;

  END TRY
  BEGIN CATCH
    IF ERROR_STATE() = 99
    BEGIN
      DECLARE @ErrorMessage NVARCHAR(4000);
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;

      SELECT 
          @ErrorMessage = ERROR_MESSAGE(),
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE();

      RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
      RETURN ;
    END
    -- Do nothing and continue execution, if column doesn't exist
  END CATCH

This code would check only the existence of forbidden_column_name1. The others are not checked, because an exception has to be raised during the check of forbidden_column_name1: either from the sp_executesql, if column doesn't exist, or from the RAISERROR, if the column does exist. Either way, the execution passes to catch block and never comes back.

My question is, is there a way to force the execution go back to the TRY block, if an error is not actually an error, but actually something that is desired. Otherwise I have to put all checks in separate TRY/CATCH blocks, which makes the code seem a little bit redundant.

---

Why not query sys.columns, to determine the existence or not of these columns, rather than trying to infer their existence by querying the table?

---

e.g.

IF EXISTS(select * from sys.columns where object_id = OBJECT_ID(@table_name) and name in (
    'forbidden_column1','forbidden_column2','forbidden_column3'))
BEGIN
    RAISERROR('One or more forbidden columns detected. Review the documentation',16,99)
    RETURN
END

--Proceed, knowing that the table doesn't contain the forbidden columns

---

You would have to wrap each individual select statement in a try/catch block.

BEGIN TRY 

    SET @sql = N'SELECT TOP 0 forbidden_column_name1 INTO #t1 FROM ' + @input_table ;
    EXEC @err = sp_executesql @sql ;
    IF @err = 0  -- i.e. if succeeds
        RAISERROR('Input table cannot contain ''forbidden_column_name1''. This name is reserved!', 16, 99) ;
END TRY
BEGIN CATCH
    IF ERROR_STATE() = 99
        EXEC usp_RethrowError
END CATCH

To avoid repeating yourself, you could create a procedure for this that has parameters for both the name of the table, and the name of the column.

Definition of usp_RethrowError

---

Not a direct answer to your question but you're better off using INFORMATION_SCHEMA to determine if the columns exist.

Something like:

IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 
    TABLE_NAME = @input_table AND COLUMN_NAME IN ('forbidden_column_name1', 'forbidden_column_name2', 'etc'))
BEGIN
    RAISERROR('idiot', 16, 99)
END