Using a Stored Procedure extended property to preserve custom logic in the CSLA generated code.

In the previous article on how to use the partial methods in the CodeSmith CSLA templates to preserve custom codemostly focused on the code side of things. For those of you who might be using Stored Procedures might be wondering... How do I accomplish preserving my custom stored procedure logic? Don't worry, CodeSmith already though of this and didn't leave you out in the dark! The CSLA templates by default will not automatically execute the generated stored procedures but it can! To do this you need to set the AutoExecuteStoredProcedures property to True. Now, when your database related metadata changes so will your stored procedures.

This is a great feature until you need to implement custom logic into your stored procedure. By default we will generate logic that will prevent a stored procedure from being overwritten if you have AutoExecuteStoredProcedures turned to true or you try to drop the stored procedure. Here is the example that we are going to be modifying that will result in the same exact output as our previous sample.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROPTABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping CSLA_Calendar_Select'
GO
IF EXISTS(SELECT 1 FROM fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE','CSLA_Calendar_Select', default, default) WHERE name = 'CustomProcedure' and value = '1')
BEGIN
    RAISERROR ('The procedure CSLA_Calendar_Select has an Extended Property "CustomProcedure" which means is has been customized. Please review and remove the property if you wish to drop the procedure.',16,1)
    INSERT INTO #tmpErrors (Error) SELECT 1
END
GO
 
IF OBJECT_ID(N'CSLA_Calendar_Select') IS NOT NULL
    DROP PROCEDURE CSLA_Calendar_Select
 
GO
IF @@ERROR!=0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
 
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
 
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
 
IF @@TRANCOUNT>0 BEGIN
PRINT 'The stored procedure drop has succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The stored procedure drop has failed'
GO
 
DROP TABLE #tmpErrors
GO
 
--region [dbo].[CSLA_Calendar_Select]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Blake Niemyjski using CodeSmith: v5.2.3, CSLA Templates: v3.0.0.1888, CSLA Framework: v3.8.4
-- Procedure Name: [dbo].[CSLA_Calendar_Select]
------------------------------------------------------------------------------------------------------------------------
 
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROPTABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
 
PRINT N'Creating [dbo].[CSLA_Calendar_Select]'
GO
 
IF EXISTS(SELECT 1 FROM fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE','CSLA_Calendar_Select', default, default) WHERE name = 'CustomProcedure' and value = '1')
    BEGIN
        RAISERROR ('The procedure [dbo].[CSLA_Calendar_Select] has an Extended Property "CustomProcedure" which means is has been customized. Please review and remove the property if you wish to create the stored procedure.',16,1)
        INSERT INTO #tmpErrors (Error) SELECT 1
    END
GO
 
CREATE PROCEDURE [dbo].[CSLA_Calendar_Select]
    @p_ID nvarchar(50) = NULL,
    @p_CalendarName nvarchar(50) = NULL,
    @p_Name nvarchar(50) = NULL,
    @p_EventStart datetime = NULL,
    @p_EventEnd datetime = NULL,
    @p_Resource nvarchar(150) = NULL
AS
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
    [ID],
    [CalendarName],
    [Name],
    [EventStart],
    [EventEnd],
    [Resource]
FROM
    [dbo].[Calendar]
WHERE
    ([ID] = @p_ID OR @p_ID IS NULL)
    AND ([CalendarName] = @p_CalendarName OR @p_CalendarName IS NULL)
    AND ([Name] = @p_Name OR @p_Name IS NULL)
    AND ([EventStart] = @p_EventStart OR @p_EventStart IS NULL)
    AND ([EventEnd] = @p_EventEnd OR @p_EventEnd IS NULL)
    AND ([Resource] = @p_Resource OR @p_Resource IS NULL)
 
GO
IF @@ERROR!=0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
 
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
 
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'Stored procedure creation succeeded.'
COMMIT TRANSACTION
END
ELSE PRINT 'Stored procedure creation failed.'
GO
DROP TABLE #tmpErrors
GO
 
--endregion

Let's open up SQL Management Studio and run the script above. Once you run the query above, you can see that the table was dropped and recreated.

Dropping CSLA_Calendar_Select

The stored procedure drop has succeeded

Creating [dbo].[CSLA_Calendar_Select]

Stored procedure creation succeeded.

Now that you can see that the stored procedure is being overwritten, let's modify the stored procedure to return the same results as our previous example.

ALTER PROCEDURE [dbo].[CSLA_Calendar_Select]
    @p_ID nvarchar(50) = NULL,
    @p_CalendarName nvarchar(50) = NULL,
    @p_Name nvarchar(50) = NULL,
    @p_EventStart datetime = NULL,
    @p_EventEnd datetime = NULL,
    @p_Resource nvarchar(150) = NULL
AS
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
    [ID],
    [CalendarName],
    [Name],
    [EventStart],
    [EventEnd],
    [Resource]
FROM
    [dbo].[Calendar]
WHERE
    ([ID] = @p_ID OR @p_ID IS NULL)
    AND ([CalendarName] = @p_CalendarName OR @p_CalendarName IS NULL)
    AND ([Name] = @p_Name OR @p_Name IS NULL)
    AND ([EventStart] = @p_EventStart OR @p_EventStart IS NULL)
    AND ([EventEnd] = @p_EventEnd OR @p_EventEnd IS NULL)
    AND ([Resource] = @p_Resource OR @p_Resource IS NULL)
    AND [EventStart] >= CONVERT(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0),101)

Since you modified the stored procedure, you want to mark it as a custom Stored Procedure. If you choose not to do this, then you will need to modify this stored procedure any time you wish to regenerate or drop and recreate this stored procedure. This can be done by right clicking on the stored procedure, selecting properties and then selecting the Extended Properties node. You will want to create a new extended property with a name of CustomProcedure and a value of 1. The following SQL Script will also create the extended property.

EXEC sys.sp_addextendedproperty @name=N'CustomProcedure', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CSLA_Calendar_Select'

GO

Now when you execute the SQL script to drop and create the stored procedure it will error out and preserve your changes!

Dropping CSLA_Calendar_Select

Msg 50000, Level 16, State 1, Line 3

The procedure CSLA_Calendar_Select has an Extended Property "CustomProcedure" which means is has been customized. Please review and remove the property if you wish to drop the procedure.

(1 row(s) affected)

The stored procedure drop has failed

Creating [dbo].[CSLA_Calendar_Select]

Msg 50000, Level 16, State 1, Line 4

The procedure [dbo].[CSLA_Calendar_Select] has an Extended Property "CustomProcedure" which means is has been customized. Please review and remove the property if you wish to create the stored procedure.

(1 row(s) affected)

Msg 2714, Level 16, State 3, Procedure CSLA_Calendar_Select, Line 13

There is already an object named 'CSLA_Calendar_Select' in the database.

(1 row(s) affected)

Stored procedure creation failed.