CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
hussian
hussian

Reputation: 399

(SQL Server) SQL doesn't allow creating a procedure after checking if it exists or not

Below is the sql I am trying to use to check if the store procedure doesnot exists then create the procedure. it throws an error: Incorrect syntax near the keyword 'PROCEDURE'

IF NOT EXISTS (SELECT * FROM SYS.procedures WHERE name = N'[Reports].[usp_EMS_Stats_DashboardPendingBookingSel]')
BEGIN

CREATE PROCEDURE [Reports].[usp_EMS_Stats_DashboardPendingBookingSel]   @OrganisationID INT, @Category2 varchar(30) = NULL
AS
  BEGIN
     --SOME SQL..
  END
END

Upvotes: 2

Views: 75

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062820

The schema is not included in the name; the name is just usp_EMS_Stats_DashboardPendingBookingSel, and you can check the schema via

schema_id = SCHEMA_ID('Reports')

Aside: note that it is actually sys.procedures, not SYS.procedures - this is important if the server is configured to be case-sensitive.

ALTER PROC / CREATE PROC must be the first statement in a batch, so you need to be a little creative in how you do this. In the general case of wanting a re-runnable create-or-update script, the following approach works:

IF OBJECT_ID(N'[Reports].[usp_EMS_Stats_DashboardPendingBookingSel]') IS NULL
BEGIN
    EXEC('CREATE PROCEDURE [Reports].[usp_EMS_Stats_DashboardPendingBookingSel] AS BEGIN PRINT ''impl'' END');
END
GO
ALTER PROCEDURE [Reports].[usp_EMS_Stats_DashboardPendingBookingSel]   @OrganisationID INT, @Category2 varchar(30) = NULL
AS
BEGIN
     PRINT 'HI'; -- your actual code here
END

Upvotes: 1

Related Questions