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! 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 sample.
SET NUMERIC_ROUNDABORT OFF
GO
...
our previous article.
| Code Block |
|---|
|
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_ |
NULLS sysobjects id=OBJECT_ID('tempdb..#tmpErrors')) |
DROPTABLE #tmpErrorsGO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping 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' |
1 FROM 1 FROM fn_listextendedproperty (NULL, |
'PROCEDURE','CSLA_Calendar_Select', |
BEGIN
RAISERROR
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
INSERT INTO #tmpErrors (Error) |
GO
IF
GO
IF OBJECT_ID(N'CSLA_Calendar_Select') |
NULL DROP PROCEDURE NULL
DROP PROCEDURE CSLA_Calendar_Select |
GO
IF 0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTIONGO
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 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:
-- Procedure Name: [dbo].[CSLA_Calendar_Select] |
------------------------------------------------------------------------------------------------------------------------ |
SET ROUNDABORT ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_ |
NULLS sysobjects id=OBJECT_ID('tempdb..#tmpErrors')) |
DROPTABLE #tmpErrorsGO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating 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 1 FROM 1 FROM fn_listextendedproperty (NULL, |
'PROCEDURE','CSLA_Calendar_Select', |
BEGIN
RAISERROR
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
INSERT INTO #tmpErrors (Error) |
1 END
GO
CREATE PROCEDURE 1
END
GO
CREATE PROCEDURE [dbo].[CSLA_Calendar_Select] |
@p_CalendarName nvarchar(50) = |
@p_Resource nvarchar(150) = |
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[ID],
[CalendarName],
[Name],
[EventStart],
[EventEnd],
[Resource]
FROM
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[ID],
[CalendarName],
[Name],
[EventStart],
[EventEnd],
[Resource]
FROM
[dbo].[Calendar] |
WHERE
ID ID AND
AND ([CalendarName] = @p_ |
CalendarName CalendarName AND Name Name AND EventStart EventStart AND EventEnd EventEnd AND Resource Resource
GO
IF 0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTIONGO
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
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.
| Code Block |
|---|
|
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.
...
...
...
[dbo].[CSLA_Calendar_Select] |
...
...
...
@p_CalendarName nvarchar(50) = |
...
...
...
...
...
...
...
...
@p_Resource nvarchar(150) = |
...
...
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[ID],
[CalendarName],
[Name],
[EventStart],
[EventEnd],
[Resource]
FROM
...
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[ID],
[CalendarName],
[Name],
[EventStart],
[EventEnd],
[Resource]
FROM
[dbo].[Calendar] |
...
WHERE
...
...
...
...
...
...
AND ([CalendarName] = @p_ |
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
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.
sys.sp_addextendedproperty |
@name @name=N'CustomProcedure', @value=N'1' |
, @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CSLA_Calendar_Select' |
Now when you execute the SQL script to drop and create the stored procedure it will error out and preserve your changes!
| Code Block |
|---|
|
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. |
...
...
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. |
...
...
Msg 2714, Level 16, State 3, Procedure CSLA_Calendar_Select, Line 13 |
...
There is already an object named 'CSLA_Calendar_Select' in the database. |
...
...
Stored procedure creation failed. |