Search code examples
sql-server-2005stored-proceduressql-server-2000compatibilitysql-drop

How to drop Stored Procedures in a SQL 2000 + SQL 2005 compatible manner?

I have a project that requires me to do development in SQL Server 2005, but do deployments to a SQL Server 2000 box.

For 99% of the SQL code, I have no problems, everything appears to be backwards compatible.

Now I am just about to start adding all the Stored Procedures (SPs) to source control, and I like the idea of doing a drop-add each time the query is executed. I.E. If the SP already exists, first drop it. Then create/re-create the SP.

How do I do this in a single script, in a manner that is compatible with both SQL 2000 and SQL 2005, so that my scripts will just work during Development (2000) AND Production (2005)? I believe the syntax is slightly different, and the SP metadata is stored in different system tables.

Please assist with a working SQL script.

Solution

  • This works for both SQL 2000 and SQL 2005. I have tested it right now.

    USE databasename
GO

IF object_id('schema.StoredProcedureName') IS NOT NULL
DROP PROCEDURE schema.StoredProcedureName
GO

CREATE PROCEDURE schema.StoredProcedureName
.. your code