(T-SQL) Check if a procedure exists before creating it.

Question

I decided to edit my post since people didn't quite get what I'm trying to do (I suppose it was not irrelevant to say why I needed it after all):

Ok, there we go. The thing is, I have a HUGE SQL script which many clients use and it has to be ran thoroughly every time a client executes the "database management" functionality that our software provides. So some of these clients might already have the procedure stored upon running the script, and some may not. I know this is stupid, I don't actually need this procedure to remain unstored, I can just check if it exists and create it if it doesn't. However, it doesn't matter how much I try to bend T-SQL syntax, there's always an error (always related to the fact that "CREATE/ALTER PROCEDURE' must be the first statement in a query batch"). That's why I came up with the drop before create thing. And I read around the web that it's the only way to do it, but I don't like the way it's done (suppose that should have been my post in the first place).

Original Post:

Hello. After some research, I found info only regarding PL/SQL on the matter, but what is proposed there doesn't work with T-SQL. I think it's irrelevant to point out why I need a non-stored procedure so you'll just gonna have to take my word when I say that I do. For now, I'm doing this:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO

CREATE PROCEDURE MyProc
...

It works, but I wanted to know if there's a more effective/elegant way to do it?

Answer 1

The purpose of this is confusing ... how would you expect a query to live on SQL Server and NOT be a stored procedure. Unless, you are just using a SELECT query which can be a View. But it's still "stored" on the server. Perhaps you just want to use in-line SQL (SQL statements in your source code)? However, this isn't always ideal for various reasons.

Answer 2

This is the standard way of creating a data base when you're adding new elements and don't want to disrupt the existing entities.

Answer 3

If you need to modify a stored procedure before each launch, then maybe you can store its text as mere text in a varchar(max) column and execute it using sp_executesql.

Although I can't see why ALTER PROCEDURE would be worse than that.

Answer 4

You can run procedural code anywhere you are able to run a query.

Just copy everything after AS:

BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END

This code does exactly same things a stored proc would do, but is not stored on the database side.

That's much like what is called anonymous procedure in PL/SQL.

Update:

Your question title is a little bit confusing.

If you only need to create a procedure if it not exists, then your code is just fine.

Here's what SSMS outputs in the create script:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'myproc') AND type in (N'P', N'PC'))
DROP …
CREATE …

Answer 5

I realize this has already been marked as answered, but we used to do it like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
   exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE [dbo].[MyProc] 
AS
  ....

Just to avoid dropping the procedure.