How can i implement a 'IF NOT Exists Create Procedure with Parameter'?

Question

I'm trying to do the following

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'test' AND type = 'P')
BEGIN
    CREATE PROCEDURE test (@val1 INT)
END

Just getting an error of incorrect syntax.

You want to read this question and the *second* answer: https://stackoverflow.com/questions/2072086/how-to-check-if-a-stored-procedure-exists-before-creating-it — Philip Kelley, Jul 31 '19 at 18:18

score 2 · Answer 1 · answered Jul 31 '19 at 19:10

Found a solution that worked for me as i wanted to. That is, create a procedure with a parameter if it doesn't exist.

IF OBJECT_ID('dbo.test') IS NULL
BEGIN
EXEC('CREATE PROCEDURE test(@val1 INT) AS SELECT  * FROM sys.objects')
END

Thank you guys for all your help.

score 0 · Answer 2 · answered Jul 31 '19 at 18:22

0

There seems no error with - IF NOT EXISTS(SELECT * FROM sys.objects WHERE name = 'test' AND type = 'P')

Try using

BEGIN 
 CREATE PROCEDURE test(@val1 INT) AS
  SELECT  * FROM sys.objects
  END

Use PROCEDURE instead of PROC , Define your query in procedure as required and you are all set. :)

answered Jul 31 '19 at 18:22

ManushiKapoor

Still get the same error. Don;t think using PROCEDURE in full makes any difference. The problem seems with creating the procedure in between begin and end for the if statement, – Clin Turn Rodriguez Jul 31 '19 at 18:32

2 Answers2