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?

• Lennart - Slava Ukraini: Have you tried creating the procedure without the try-catch block?
• Gavin: Why do you want the create statement in a try catch? What error are you trying to handle? I can understand the try catch inside the procedure but can’t see a reason for it on a create

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.