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.
| Code Block | ||
|---|---|---|
| ||
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.
...