Create stored procedure inside try-catch block

Question

I'm using sqlserver 2014 and trying to create a StoredProcedure inside try-catch block like this:

BEGIN TRY   
  CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT '' 
END TRY
BEGIN CATCH
  ...
END CATCH

It fails to run with the error of: Incorrect syntax near 'SELECT' Expecting EXTERNAL.
Then i changed sp like: CREATE PROCEDURE [ammeghezi1] (@w INT) AS BEGIN SELECT '' END (cover sp with BEGIN-END block), But the error did not change. I also add GO after BEGIN TRY statement and it just got worst.
I'm getting to conclude that creating sp inside TRY_CATCH block is not practical.

Is this even possible to create sp inside TRY_CATCH block? And how?

Answer 1

From the CREATE PROCEDURE documentation:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

If you need a try/catch, you'll need to execute the DDL using dynamic SQL so that it is in a separate batch:

BEGIN TRY   
    DECLARE @CreateProcedureStatement nvarchar(MAX) =
N'CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT ''''';
    EXEC sp_executesql  @CreateProcedureStatement;
END TRY
BEGIN CATCH
    THROW;
END CATCH
GO

Answer 2

Interesting question, but I sense a bad practice here (this is a SQL developer perspective, not a DBA one). Why would you do that?

If you the procedure must be created only it does not exist, just check for its existence:

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[MyProc]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )

The context of how this is run is not specified, but it might be important. If this is part of a deployment process, typically it is a good idea to let the exception (execution error) bubble and handle it outside SQL.

While having the stored procedure executed as a dynamic SQL works, I would advice against it: it is way harder to write its creation as a string, it looks awful (way less readable) and all the parsing errors will be moved to run time.