Avoid Overwriting Modified SPs With Code Generation

One of the challenges of using code generation is to avoid overwriting your custom changes when you re-generate the code later.

This is a trick I am using to avoid overwriting my stored procedures if I update them manually.  The trick is to include a comment indicating that the SP has been code generated, and then to remove that comment if you update the SP.  So lets say that you generate:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
-- CODE GENERATED
SELECT * FROM
OrderDetails
WHERE
OrderId = @OrderId

and you made some customizations to the SP so it now looks like this:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
SELECT * FROM
OrderDetails
INNER JOIN
Orders ON
Orders.OrderId = OrderDetails.OrderId
WHERE
OrderId = @OrderId

The following code will help you when generating your SPs to avoid overwriting this change:

IF EXISTS(
  SELECT [definition] AS objectText
  FROM sys.sql_modules
  WHERE [object_id] = OBJECT_ID(N'OrderDetails_Get', 'P')
  AND definition LIKE '%CODE GENERATED%')
BEGIN
   -- overwrite is ok
END

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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