Cómo comprobar si existe un procedimiento almacenado antes de crearlo
Tengo un script SQL que debe ejecutarse cada vez que un cliente ejecuta la funcionalidad de "administración de base de datos". El script incluye la creación de procedimientos almacenados en la base de datos del cliente. Es posible que algunos de estos clientes ya tengan el procedimiento almacenado al ejecutar el script y otros no. Necesito agregar los procedimientos almacenados que faltan a la base de datos del cliente, pero no importa cuánto intente modificar la sintaxis de T-SQL, obtengo
CREATE/ALTER PROCEDURE' debe ser la primera declaración en un lote de consultas
He leído eso de dejar caer antes de crear obras, pero no me gusta hacerlo de esa manera.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO
CREATE PROCEDURE MyProc
...
¿Cómo puedo verificar la existencia de un procedimiento almacenado y crearlo si no existe, pero modificarlo si existe?
Me doy cuenta de que esto ya se ha marcado como respondido, pero solíamos hacerlo así:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))
exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER PROCEDURE [dbo].[MyProc]
AS
....
Sólo para evitar abandonar el procedimiento.
Puede ejecutar código de procedimiento en cualquier lugar donde pueda ejecutar una consulta.
Simplemente copia todo después AS:
BEGIN
DECLARE @myvar INT
SELECT *
FROM mytable
WHERE @myvar ...
END
Este código hace exactamente lo mismo que haría un proceso almacenado, pero no se almacena en el lado de la base de datos.
Esto es muy parecido a lo que se llama procedimiento anónimo en PL/SQL.
Actualizar:
El título de tu pregunta es un poco confuso.
Si sólo necesita crear un procedimiento si no existe, entonces su código está bien.
Esto es lo que SSMSgenera el script de creación:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'myproc')
AND type IN ( N'P', N'PC' ) )
DROP …
CREATE …
Actualizar:
Ejemplo de cómo hacerlo al incluir el esquema:
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[MyProc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[MyProc]
END
En el ejemplo anterior, dbo es el esquema.
Actualizar:
En SQL Server 2016+, puedes simplemente hacer
CREATE OR ALTER PROCEDURE dbo.MyProc
Si está buscando la forma más sencilla de verificar la existencia de un objeto de base de datos antes de eliminarlo, esta es una forma (el ejemplo utiliza un SPROC, como el ejemplo anterior, pero podría modificarse para tablas, índices, etc.):
IF (OBJECT_ID('MyProcedure') IS NOT NULL)
DROP PROCEDURE MyProcedure
GO
Esto es rápido y elegante, pero debe asegurarse de tener nombres de objetos únicos en todos los tipos de objetos, ya que no lo tiene en cuenta.
Sé que desea "ALTERAR un procedimiento si existe y crearlo si no existe", pero creo que es más sencillo:
- Descarte el procedimiento (si ya existe) y luego
- Vuelva a crearlo.
Como esto:
IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
DROP PROCEDURE MyProcedure
GO
CREATE PROCEDURE MyProcedure AS
BEGIN
/* ..... */
END
GO
El segundo parámetro indica OBJECT_IDque solo se busquen objetos con object_type = 'P', que son procedimientos almacenados:
AF = Función agregada (CLR)
C = VERIFICAR restricción
D = DEFAULT (restricción o independiente)
F = restricción de CLAVE EXTRANJERA
FN = función escalar SQL
FS = Función escalar de ensamblaje (CLR)
FT = Función con valores de tabla de ensamblaje (CLR)
IF = función SQL en línea con valores de tabla
TI = Tabla interna
P = Procedimiento almacenado SQL
PC = Procedimiento almacenado de ensamblaje (CLR)
PG = Guía de planos
PK = restricción de CLAVE PRIMARIA
R = Regla (estilo antiguo, independiente)
RF = Procedimiento de filtro de replicación
S = Tabla base del sistema
SN = Sinónimo
SO = Objeto de secuencia
TF = función con valores de tabla SQL
TR = Disparador
Puede obtener la lista completa de opciones a través de:
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
