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

Question

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
}

Solution 1

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

Solution 2

For those who use Entity Framework and a DbContext:

create an extension class for DbContext:

internal static class DbContextExtensions
{
    public static bool StoredProcedureExists(this DbContext context,
        string procedureName)
    {
        string query = String.Format(
            @"select top 1 from sys.procedures " +
              "where [type_desc] = '{0}'", procedureName);
        return dbContext.Database.SqlQuery<string>(query).Any();
    }
}

As robIII remarked, this code should not be published to the outside world as it makes the database vulnerable for hackers (thank you RobIII!). To prevent this use a parameterized statement. The problem with the above mentioned method is described here

The solution is to put procedureName as a parameter in an SQL statement. SQL will check if the string parameter has the desired format, thus inhibiting malicious calls:

public static bool ImprovedExists(this DbContext dbContext, string procedureName)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"procedurename", procedureName),
    };
    const string query = @"select [name] from sys.procedures where name= @procedurename";
    return dbContext.Database.SqlQuery<string>(query, functionParameters).Any();
}

Solution 3

I found this on MSDN

select * from sys.objects where type_desc = 'SQL_STORED_PROCEDURE' AND name = 'Sql_PersonInsert'

Solution 4

My stab at it:

• Reusable extension method
• Minimal Sql / Minimal C#
• Called from .Net as the OP implicitly requested
• Could be faster because of the object_id function

public static bool StoredProcedureExists(this string source)
{
  using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
  {
    conn.Open();
    using (var cmd = new SqlCommand($"select object_id('{source}')", conn))
      return !cmd.ExecuteScalar().ToString().IsNullOrWhiteSpace();
  }
}

Solution 5

Try:

SELECT * 
FROM sys.objects 
WHERE object_id = OBJECT_ID(N'GP_SOP_AdjustTax') AND type in (N'P', N'PC')

View more solutions

Comments

[Stian Standahl]

why are you doing the reader.Read() in a while loop ? wouldnt it be sufficient to have an if check?

[Sirwan Afifi]

If you really are going to bring back more than 1 row, then you should put it in a while loop. and If you expect only 0 or 1 rows, you can have if check

[RobIII]

Could you check if the stored procedure "'; drop table users; --" exists? ;-) Use parameterized statements!

[RobIII]

...or just use select top 1 ....; that will ensure you will always get 0 (does not exist) or 1 (exists) row.

[Daniel James Bryars]

This isn't schema aware, you will get false positives with procedures with the same name and different schemas. For example dbo.MyStoredProcedureName, and SomeSchema.MyStoredProcedureName. It also doesn't normalise the names, so [MyStoredProcedureName] doesn't return any results. The answer by Jakub Konecki below deals with both these cases.