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

c#, sql, sql-server, stored-procedures

Solution

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;
            }
        }
    }
}

Problem

I tried below code for cheking SP is alredy exist or not. if not exist i am creating.. But every time it is showing sp is not created.....But my database already have this sp. Let me know where i am doing mistake. ``` string checkSP = String.Format( "IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", "GP_SOP_AdjustTax"); SqlCommand command = new SqlCommand(checkSP, myConnection); command.CommandType = CommandType.Text; if (myConnection == null || myConnection.State == ConnectionState.Closed) { try { myConnection.Open(); } catch (Exception a) { MessageBox.Show("Error " + a.Message); } } bool Exist = false; Exist = Convert.ToBoolean(command.ExecuteScalar()); if (Exist == false) //false : SP does not exist { // here i am writing code for creating SP } ```

Original source