We prefer to use ALTER PROCEDURE rather than DROP / CREATE PROCEDURE.
To facilitate this then if the Sproc does not exist we first create a STUB for it, and then use ALTER to create the actual SProc using this code
IF OBJECT_ID(N'[dbo].[temp_SP_Procedure]', N'P') IS NULL EXEC ('CREATE PROC dbo.temp_SP_Procedure AS PRINT ''temp_SP_Procedure:stub version, full version not found''') GO ALTER PROCEDURE dbo.temp_SP_Procedure AS ....
but we run the risk that there is a syntax error in the main code and only the STUB is created. Therefore I was wanting to create a STUB that would fail-safe. What would be the Best Practice for the Stub? (I'd like to keep the code as short as possible). Here's my suggestion:
-- Create STUB for a Stored Procedure - to be created usign EXEC ('SQL syntax as a string') CREATE PROC dbo.temp_SP_Procedure AS IF @@TRANCOUNT >= 1 BEGIN ROLLBACK END RAISERROR('temp_SP_Procedure:stub version, full version not found', 10, 1) WITH LOG, SETERROR RETURN -1 GO
One problem with this is that the @@ERROR value does not persist to the return from the Sproc - so cannot be retrieved by the caller.