3

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?

  • 1
    Have you tried creating the procedure without the try-catch block? – Lennart May 19 '18 at 9:38
  • 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 – Gavin May 21 '18 at 6:10
5

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
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.