CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
unj2
unj2

Reputation: 53481

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

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?

Upvotes: 1

Views: 2136

Answers (5)

Chirag
Chirag

Reputation: 4186

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

Upvotes: 2

Alex
Alex

Reputation: 2335

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.

Upvotes: 1

Waqar Janjua
Waqar Janjua

Reputation: 6123

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

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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.

Upvotes: 3

isakavis
isakavis

Reputation: 785

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

Upvotes: -1

Related Questions