Supprimer une procédure stockée SQL Server sans erreur

Comment supprimer une procédure stockée SQL Server sans erreur, si elle existe ?

Pour supprimer une procédure stockée SQL Server et éviter les erreurs, tester l’existence de la procédure dans la base SQL Server pour conditionner la commande DROP PROCEDURE. D’autre part, c’est une bonne pratique de toujours tester l’existence des objets SQL Server. En l’occurrence une procédure stockée avec la commande IF EXISTS.

Table des matières

Script pour supprimer une procédure stockée

Voici la commande T-SQL de base pour supprimer une procédure stockée SQL Server:

DROP PROCEDURE dbo.uspGetClient;
GO

Supprimer une procédure MS SQL sans erreur

Premièrement, comment effacer une procédure stockée SQL Server sans générer une erreur ? C’est à dire vérifier si l’objet existe avant de le supprimer avec la commande SQL DROP. Pour cela, utiliser la fonction T-SQL IF EXISTS. Comme sur cet exemple, sur les versions SQL Server antérieures à la version 2016.

IF EXISTS (
	SELECT 	name 
	FROM 	sysobjects  
	WHERE 	name = 'uspGetClient' 
	AND 	type = 'P'
)
	DROP PROCEDURE dbo.uspGetClient;
GO

En effet, si la procédure n’existe pas le message d’erreur suivant est affiché, sur les versions Anglaise de SQL Server:

Cannot drop the procedure ‘uspGetClient’, because it does not exist or you do not have permission.

Test d’existence intégré depuis SQL Server 2016

A noter que depuis la version SQL Server 2016, il est possible de tester si une fonction, une table ou une procédure stockée existe et la supprimer avec une seule commande SQL.

DROP PROCEDURE IF EXISTS dbo.uspGetClient;
GO

Générer un script pour supprimer toutes les procédures stockées

Voici enfin une requête pour générer un script de type LDD pour supprimer toutes les procédées stockées de la base de données. Le code utilise la vue système sys.procedures, stockée dans la base de données système msdb.

SELECT
  'DROP PROCEDURE IF EXISTS ' + SCHEMA_NAME(schema_id) + '.' + [Name] + ';' AS [SQL_Query], 
  SCHEMA_NAME(schema_id) AS [Schema_Name],
  [object_id],
  [type],
  [type_desc],
  [create_date]
FROM sys.procedures
order by 1;

Le résultat est une colonne avec une ligne pour chaque procédure stockée de type utilisateur. Chaque ligne contient une commande pour tester l’existence et supprimer la procédure.