How to Detect If a Stored Procedure Already Exists

How to check if a stored procedure exists before creating it

You can run procedural code anywhere you are able to run a query.

Just copy everything after AS:

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 PL/SQL.

Update:

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.

Here's what 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 …

Update:

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.

Update:

In SQL Server 2016+, you can just do

CREATE OR ALTER PROCEDURE dbo.MyProc

Different ways to check if a stored procedure exists in SQL Server

The first version checks if any object exists with the given name. In that case, if you make a typo and enter a name of a table, it will still pass.
About the second version is obsoleted because there is no guarantee if future versions will support to use sysstat. (Check the msdn site: https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql)

I wouldn't use any of them. I would prefer to check objects directly by name:

if exists (select 1 from sys.procedures where name = 'procedure_to_drop')

sys.procedures: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-procedures-transact-sql

If you have sql server 2016, you can use the new language elements to do the same depending on what you want:

• CREATE OR ALTER: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql
• DROP IF EXISTS: https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-procedure-transact-sql

how to check if stored procedure exists or not in sql server using c# code

Try:

if exists(select * from sys.objects where type = 'p' and name = '<procedure name>' )

Also you can check that with c#:

string connString = "";
string query = "select * from sysobjects where type='P' and name='MyStoredProcedureName'";
bool spExists = false;
using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    using (SqlCommand command = new SqlCommand(query, conn))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                spExists = true;
                break;
            }
        }
    }
}

check if two values already exists in table stored procedure

How about using IF NOT EXISTS

CREATE PROCEDURE `ChangeDashboardContent`(TestSuiteCollectionId varchar(45), Project varchar(45))
BEGIN
  /*Here it should check if these two values already exsists*/
  IF NOT EXISTS (select * from dashboard_hidden where TestSuiteCollectionId = 
  TestSuiteCollectionId  and Project  = Project)
  BEGIN
     INSERT INTO dashboard_hidden(`TestSuiteCollectionId`,`Project`) 
     VALUES (TestSuiteCollectionId, Project);
  END 
 
END

Check if a given ID exists using Stored Procedure by If Exists or If Not Exists?

Do it without the quotes around @BatchNo:

If Exists(select BatchNo from Work_In_Progress where BatchNo = @BatchNo)

---

Related Topics