Patch to drop all procedures on generation

I created a patch to incorporate the code posted on http://community.codesmithtools.com/forums/thread/23863.aspx by smallinov

This patch changes the "IncludeDrop" option from being a bool to a new enum with the options of Entity/All/None where...

Entity - Equates to the old true i.e each stored proc generated will be preceded by a corresponding DROP statement

None - Equates to the old false i.e. no procedures will be dropped

All - All netTiers procedures will be dropped with the following caveats - the ProcedurePrefix must be specified (so we don't inadvertantly drop all procs in a db!) - any custom procs will not be touched.

What this means is that if you select "All", then after a gen you will only have those created during that run. This means you shouldn't get any orphaned procs left hanging around.

swin

Some SQL to accomplish the same thing manually.  I currently use this as part of a re-generation process (NAnt) and it is run before NetTiers.  Kudos though on having that option now in NetTiers.

-- Cleanup any old custom stored procedures that start with "csp_"
DECLARE @procedure varchar(max)
WHILE (SELECT COUNT(*) FROM sysobjects WHERE name LIKE 'csp_%' and xtype = 'P') > 0
BEGIN
    SET @procedure = (SELECT TOP 1 [name] FROM sysobjects WHERE name LIKE 'csp_%' and xtype='P')
    SET @procedure = 'DROP PROCEDURE ' + @procedure
    EXEC(@procedure)
END
GO

-- Cleanup any old NetTiers stored procedures that start with "nsp_"
DECLARE @procedure varchar(max)
WHILE (SELECT COUNT(*) FROM sysobjects WHERE name LIKE 'nsp_%' and xtype = 'P') > 0
BEGIN
    SET @procedure = (SELECT TOP 1 [name] FROM sysobjects WHERE name LIKE 'nsp_%' and xtype='P')
    SET @procedure = 'DROP PROCEDURE ' + @procedure
    EXEC(@procedure)
END
GO

One issue I found was that if you seperate your tables using shemas (SQL 2005) then the drop procedure will fail. Making the following changes to StoredProceduresXml.cst fixed the problem.

 Original:

SELECT name FROM sysobjects WHERE type = 'P' AND objectproperty(id, 'IsMSShipped') = 0
AND name LIKE '<%=ProcedurePrefix%>%' 
......
AND name <> '<%=command.Name%>' 
......
AND name <> '<%=command.Name%>' 

Modified:

SELECT '[' + sc.name + '].[' + so.name + ']' FROM sysobjects so INNER JOIN sys.schemas sc ON sc.schema_id = so.uid WHERE type = 'P' AND objectproperty(id, 'IsMSShipped') = 0
AND so.name LIKE '<%=ProcedurePrefix%>%' 
......
AND so.name <> '<%=command.Name%>' 
......
AND so.name <> '<%=command.Name%>' 

I have decided to create a patch to fix the schema issue above.

Hello,

Thanks for the patch, I have submitted an issue on our bug tracker. You can check the status of it here.

Thanks again

-Blake Niemyjski