Write intelligent query with if exist … drop.

Usually when we build a query we start the .sql file, or command in the query editor with a simple command … CREATE PROCEDURE … bla bla bla.

Ok but if I’m building a database from scratch is very probably that my procedure will be modified a lot of time. So every time we have to launch the command DROP PROCEDURE, or ALTER PROCEDURE.

This sounds good if you are building your object. But in case of you want to store as a BACKUP PROCEDURE some scripts in the network, is better to produce this scripts as ATOMIC and AUTOMATIC.

To accomplish this task, we can use a function really useful in SQL SERVER, IF [OBJECT] EXIST, DROP IT!

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N[schema].[procedure]) AND OBJECTPROPERTY(id, NIsProcedure) = 1) DROP PROCEDURE [schema].[procedure] GO CREATE PROCEDURE [schema].[procedure] AS BEGIN

So now you can run the script as much time as you want.

Leave a comment

Your email address will not be published. Required fields are marked *