How to detect if a stored procedure already exists

sql sql-server-2005 stored-procedures
GordyII picture GordyII · Jun 2, 2009 · Viewed 195.2k times · Source

I have to write a deployment script which will work if a stored procedure exists or does not exist. i.e. if it exists, then I need to alter it, otherwise create it.

How can I do this in the sql.

I am using SQL Server 2005

Answer

Andomar picture Andomar · Jun 2, 2009

If you DROP and CREATE the procedure, you will loose the security settings. This might annoy your DBA or break your application altogether.

What I do is create a trivial stored procedure if it doesn't exist yet. After that, you can ALTER the stored procedure to your liking.

IF object_id('YourSp') IS NULL
    EXEC ('create procedure dbo.YourSp as select 1')
GO
ALTER PROCEDURE dbo.YourSp
AS
...

This way, security settings, comments and other meta deta will survive the deployment.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
What is the difference between a stored procedure and a view?

I am confused about a few points: What is the difference between a stored procedure and a view? When should I use stored procedures, and when should I use views, in SQL Server? Do views allow the creation of dynamic …

Read More