Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • SQL : How to check if a stored procedure exists before creation?

    @vikram.kashyap
    Recommendations Offline Message

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 4.58k
    Comment on it

    In this article we will learn how to check if a stored procedure exists before creating it. We can use the below script which will drop the proc if it exists and then recreate it. 

    1. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ProcToBeCreated')
    2. DROP PROCEDURE sp_ProcToBeCreated
    3. GO
    4.  
    5. CREATE PROCEDURE sp_ProcToBeCreated
    6. AS
    7. BEGIN
    8. -----Proc Contents
    9. END
    10. GO

    Or we can also alter the proc in case it exists as below:

    1. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ProcToBeCreated')
    2. ALTER PROCEDURE sp_ProcToBeCreated
    3. AS
    4. BEGIN
    5. -----Proc Contents
    6. END
    7. GO
    SQL SQL Server
    Comment on it

 0 Comment(s)

Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Reset Password
Fill out the form below and reset your password: