Extending Stored Procedures
This section will show you how to preserve your custom stored procedure logic while regenerating. 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 article.
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]
------------------------------------------------------------------------------------------------------------------------
-- 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.