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

Question

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.

Answer 1

FWIW

select * from sysobjects where type = 'p'

still works in SQL 2008, so am guessing that this is still acceptable as the lowest common denominator. DMV's weren't available in 2000.

Answer 2

I think

 IF OBJECT_ID('your_sp_name') IS NOT NULL

will tell you if it is there, although I can't test on 2000 at the mo...

Answer 3

You best option is staill the compatibility views, sysobects, syscolumns, etc

Check out the following link http://msdn.microsoft.com/en-us/library/ms187376.aspx

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000.

Answer 4

It seems to me that you recreate all STORED PROCEDUREs with respect of sys.sp_refreshsqlmodule like if is described in my old answer http://stackoverflow.com/questions/2784866/im-looking-for-a-reliable-way-to-verify-t-sql-stored-procedures-anybody-got-one/2785491#2785491. The code of STORED PROCEDUREs will be one more time verified inclusive off dependencies.

Answer 5

Don't use system tables: use OBJECT_ID

I would also deploy using ALTER but maintain source control using CREATE. That is, I only ever use differential deployment scripts (with ALTER) but compare to my source control folder after release (which as CREATE)

I have both code history and simpler deployments: there is no need to drop/create all procs. What if you forget a permission for example?

I use Red Gate/SVN BTW

Answer 6

Using the INFORMATION_SCHEMA.ROUTINES view should work in SQL Server 2000, 2005, and 2008. The only downside is that the view is no longer a viable means of determining the object's schema.

But if that is not a concern, try a script like this:

USE YourDB
GO

IF EXISTS (
  SELECT * 
  FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'usp_test'
) DROP PROCEDURE usp_test
GO

CREATE PROCEDURE usp_test AS
SELECT 1 AS val
GO

EXEC usp_test
GO

Answer 7

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