Posted By: Anonymous
I have a SQL script that has to be run every time a client executes the “database management” functionality. The script includes creating stored procedures on the client database. Some of these clients might already have the stored procedure upon running the script, and some may not. I need to have the missing stored procedures added to the client database, but it doesn’t matter how much I try to bend T-SQL syntax, I get
CREATE/ALTER PROCEDURE’ must be the first statement in a query batch
I’ve read that dropping before creating works, but I don’t like doing it that way.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc') DROP PROCEDURE MyProc GO CREATE PROCEDURE MyProc ...
How can I add check for the existence of a stored procedure and create it if it doesn’t exist but alter it if it does exist?
You can run procedural code anywhere you are able to run a query.
Just copy everything after
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
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.
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 …
Example of how to do it when including the schema:
IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1 ) BEGIN DROP PROCEDURE [dbo].[MyProc] END
In the example above, dbo is the schema.
In SQL Server 2016+, you can just do
CREATE OR ALTER PROCEDURE dbo.MyProc