Stored Proc Versioning

sturquette

Hello I have a stored procedure that looks similar to this:
CREATE Proc [dbo].[MyStoredProc]
As
Declare @start datetime, @end datetime;
/*
stored procedure Text
*/
GO
CREATE Proc [dbo].[MyStoredProc];2
As
Declare @start datetime, @end datetime;
/*
stored procedure Text
*/
GO

When I run the comparison using SQL Compare between my Development and Production DB (the Production DB does not have the stored Proc). I see the difference as shown above. But when I run the Synchronization Wizard I get the following error when I use the “Synchronize databases now” option.
The following error message was returned from the SQL Server:

[102] Incorrect syntax near 'GO'.
The variable name '@start' has already been declared. Variable names must be unique within a query batch or stored procedure.

The following SQL command caused the error:

CREATE Proc [dbo].[MyStoredProc]
As
Declare @start datetime, @end datetime;
/*
stored procedure Text
*/

GO
CREATE Proc [dbo].[MyStoredProc];2
As
Declare @start datetime, @end datetime;
/*
stored procedure Text
*/

However, when I use the “Launch the script in SQL Server Management Studio” option and run the generated script without modification, it works fine.

This is a problem when I try to automate the comparison with the SQL Compare API. Could you suggest a workaround that will help me resolve this issue?

Thanks
Steve

Brian Donahue

Hi Steve,

Sorry, I don't have a workaround for you. Numbered stored procedures aren't all that well supported in SQL Compare, in fact if it needs to alter them, it isn't working properly.

It's documented at Red Gate internally, but since numbered stored procedures have been depricated it's unlikely they'll be a fix for this particular problem unless there's a huge demand for it.