SQLTeam.com | Weblogs | Forums

Creating Stub Stored Procedures (to then use ALTER PROCEDURE)


#1

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.


#2

Why not just drop the stub proc instead, if the real proc didn't overlay it? Then you get a direct error of "procedure not found".


#3

When we do a full rollout we use DROP / CREATE, ensuring that any error in the Create will mean that the SProc is "missing". As part of the rollout we have a report of expected SProcs which shows any that are missing.

In DEV and for any Patch rollout we use ALTER so that there is no time at which the SProc is not present, in order to allow continuous use and not wind up with a situation where we are spending some minutes fixing a problem, or trying to rollback the update. ALTER also preserves permissions - we include all GRANT permissions in our script file ... but ... on a production server where we are rolling out a patch there is the risk that someone has granted addition permissions not in our script file. For a major rollout that would get caught in QA, but for a patch the procedures are "lighter" :slight_smile: in order that such fixes don't get unreasonably delayed

Perhaps I have misunderstood your suggestion and you have a method of deliberately dropping the SProc if the ALTER fails? (Not sure I like the sound of that though as it would upset the continuity that I am using ALTER to try to preserve)