How do I create a Stored Procedure if it doesn't exist in TSQL

Question

I have tried this:

if object_id('a_proc22') is not null
CREATE PROCEDURE a_proc22 AS SELECT 1
go

but it gives me a syntax error.

But this seemed to compile:

if object_id('a_proc22') is not null
EXEC('CREATE PROCEDURE a_proc22 AS SELECT 1')
go

Why is the first one incorrect?

I don't know SQL-Server but I doubt that it compiles if you have a syntax error. — Luc M, Jul 25 '12 at 13:24
Is this the same problem as answered here? http://stackoverflow.com/questions/2072086/t-sql-check-if-a-procedure-exists-before-creating-it — Mikey Mouse, Jul 25 '12 at 13:24
Possible duplicate of http://stackoverflow.com/questions/937908/how-to-detect-if-a-stored-procedure-already-exists — Michael Freidgeim, May 21 '13 at 02:21

score 3 · Accepted Answer · answered Jul 25 '12 at 13:27

3

I'm guessing the error is something like "CREATE/ALTER PROCEDURE must be the first statement in a query", so, well, that means that CREATE PROCEDURE must be the first statement in a query. If you wrapped it up on an EXEC, then when its executed, it is the first statement on that query, so that's why it works.

answered Jul 25 '12 at 13:27

Lamak

69,480
12
108
116

Came here to say this. Sayeth BOL: "The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch." So it must be the first *and last* statement. – Ben Thul Jul 25 '12 at 19:04

score 2 · Answer 2 · edited Mar 25 '14 at 10:59

2

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a_proc22]')
           AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[a_proc22];
GO

CREATE PROCEDURE [dbo].[a_proc22]
AS
BEGIN 

-- Code here

END
GO

edited Mar 25 '14 at 10:59

Rikin Patel

8,848
7
70
78

answered Mar 22 '14 at 10:22

Chirag

4,046
1
33
24

score 1 · Answer 3 · answered Jul 25 '12 at 15:07

1

if object_id('a_proc22') is not null
drop procedure a_proc22 
go
create procedure a_proc22 
AS 
SELECT 1

The GO is the important thing here after the drop, you can't have create first, some SQL validation I guess for security purposes.

answered Jul 25 '12 at 15:07

Alex

2,247
1
27
37

score 0 · Answer 4 · answered Jul 25 '12 at 13:39

Your first statement is giving error because after if condition you can not place a create/alter procedure statement. Try this

if Exists(select * from sys.procedures  -- if exists then drop it
    where name = 'a_proc22')
    Drop procedure a_proc22
    GO
    CREATE PROCEDURE a_proc22 -- create the new procedure
    AS
    SELECT 1
    go

score -1 · Answer 5 · edited Jul 25 '12 at 13:25

-1

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a_proc22]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[a_proc22]
--and create here... Or you can remove the drop and create with if not exists
GO

edited Jul 25 '12 at 13:25

ig0774

39,669
3
55
57

answered Jul 25 '12 at 13:24

isakavis

773
1
12
29

The question is why did the first SQL statement fail. – Neil Knight Jul 25 '12 at 13:28
The heading is "How do I create a Stored Procedure if it doesn't exist in TSQL". I should have said that create should be the first in a query batch to answer his question. But I think the answer is correct for the heading. – isakavis Jul 25 '12 at 14:04

How do I create a Stored Procedure if it doesn't exist in TSQL

5 Answers5