Structuring a Stored Procedure Script File

When I make changes to a stored procedure, function or trigger I usually create a script and put it into a folder that is part of a source control system.

In the script I use the following Sql to drop the stored procedure/function/trigger. This means that the script will always work and will always result in the stored procedure(etc.) being created. (Yes I know that the script still might fail if there are errors in it.)

When creating scripts I usually start with the following code which drops the stored procedure (obviously the drop is altered for triggers and functions) :

IF EXISTS (
SELECT 1 FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[MySproc]'))
BEGIN
DROP PROCEDURE [dbo].[MySproc];
PRINT '[dbo].[MySproc] - Dropped';
END

GO

I then put the stored procedure/function/trigger definition and another “GO” obviously!

Then at the end of the procedure definition, after the GO, I put the following:

PRINT '[dbo].[MySproc] - Created';
GO

Note that the PRINT’s are not needed, but I try and make it a habit to put them in anyway.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: