Since I have converted the developers at work to the new dev database I created Im now working on copying objects from dev to live on each promotion. I know Red Gate has an excellent product for this (for $600), but Im trying to see if what I need can be done with stored procedures and user defined functions.

I have the stored procedure and user defined function to copy a stored procedure

Stored Procedure:

CREATE PROCEDURE PromoteFromDev @ProcedureName VARCHAR(100)
AS
BEGIN
DECLARE @Proc VARCHAR(MAX)
IF @ProcedureName IS NOT NULL
    SELECT @Proc = YourDatabase.dbo.udfGetProcedureScript(@ProcedureName)
IF @Proc IS NOT NULL
    EXEC @Proc
END

User Defined Function

CREATE FUNCTION dbo.udfGetProcedureScript(@ProcedureName VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Proc VARCHAR(MAX)
    SELECT 
		@Proc = REPLACE((SELECT SysComments.Text AS "data()"
    FROM 
		SysComments INNER JOIN Sysobjects 
		 ON SysObjects.ID = SysComments.ID 
	WHERE 
		Sysobjects.name = @ProcedureName FOR XML PATH('')),'
','')
    RETURN @Proc
END

Now what I need to come up with is a procedure that can compare, say the stored procedures on dev versus live so I have a list of procedures that need to be merged. Anyone got any ideas?

i was at a presentation of the new visualstudio 2 months ago. Actualy i am not a great lover of microsoft and a colleague tricked me into comming.
However, there on that presentation, they described exactly what you are asking for.
And as far as i am aware of it is the first environment where this is possible.
You connect a sqlserver (yes, only ms sqlserver unfortunately) instance to your development environment (and i think you need to have the database specific version of this new visualstudio, or the very expensive all-in-one version) and then you can
- manage your source, stored procedures, database layout, testdata, etc
- couple this source into a (new) versionmanagement system
- roll out from development to production (!!) both code and data.

I was impressed. Not sure if there are other sollutions by other brands/groups. If there are not (yet) its looked realy handy, something that could be real boon in database development.


One last note: are we now using this? no. it was deemed too expensive.

I think what you're referring to is SQL 2005 and unfortunately I'm still with SQL 2000. Ill check into what you said and see if I was wrong though

Its actually visual studio 2008, even worse

This is one of those things that makes you say "why did nobody make this yet?"
If you find anything, please drop a message, there are undoubtedly more people with the same issues!

We decided, for now, to just purchase Red Gate's SQLCompare, but you know me, I'm not satisfied with that so I'm still working towards something and will post what I end up with