如何使用c#代码检查sql server中是否存在存储过程

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

提问by Kavitha

I tried below code for cheking SP is alredy exist or not. if not exist i am creating..

我试过下面的代码来检查 SP 是否已经存在。如果不存在我正在创建..

But every time it is showing sp is not created.....But my database already have this sp.

但是每次都显示sp没有创建.....但是我的数据库已经有了这个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
}

采纳答案by Sirwan Afifi

Try:

尝试：

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

Also you can check that with c#:

你也可以用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;
            }
        }
    }
}

回答by Gregor Primar

You can check with following tsql query (suitable for SQL Server):

您可以检查以下 tsql 查询（适用于 SQL Server）：

select * from sysobjects where ytype='P' and name='MyStoredProcedureName'

If query returns row then stored procedure named 'MyStoredProcedureName' exists.

如果查询返回行，则存在名为“MyStoredProcedureName”的存储过程。

And here is how you can use it in code:

下面是如何在代码中使用它：

        //TODO: set connection string
        string connString = "";
        string query = "select * from sysobjects where ytype='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;
                    }
                }
            }
        }

回答by Jakub Konecki

Try:

尝试：

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

回答by The_Cthulhu_Kid

I found this on MSDN

我在MSDN上找到了这个

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

回答by Soner G?nül

Try this;

尝试这个;

if object_id('YourStoredProcedureName') is null
    exec ('create procedure dbo.YourSp as select 1')
go
alter procedure dbo.YourStoredProcedure
as

回答by Philip Wade

private static bool StoredProcedureExists(string sp)
{    
      var connString = @"<your string here>";
      var query = string.Format("SELECT COUNT(0) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = '{0}'", sp);
      using (var conn = new SqlConnection(connString))
      {
          conn.Open();
          using (var cmd = new SqlCommand(query, conn))
          {
              return Convert.ToInt32(cmd.ExecuteScalar()) > 0;  
          }
      }
 }

回答by TobiasWempe

string checkSP = String.Format(
  "IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", 
  "GP_SOP_AdjustTax");

is fine if you change the 'U' to 'P'. With 'U' you query for user-tables, where 'P' gives you stored-procedures.

如果您将“U”更改为“P”就可以了。使用 'U' 查询用户表，其中 'P' 为您提供存储过程。

回答by Harald Coppoolse

For those who use Entity Framework and a DbContext:

对于那些使用实体框架和 DbContext 的人：

create an extension class for DbContext:

为 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

正如 robIII 所说，这段代码不应该对外发布，因为它会使数据库容易受到黑客攻击（谢谢 RobIII！）。为了防止这种情况，请使用参数化语句。上述方法的问题描述为here

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

解决方法是将procedureName作为参数放在SQL 语句中。SQL 将检查字符串参数是否具有所需的格式，从而阻止恶意调用：

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

回答by toddmo

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_idfunction

• 可重用的扩展方法
• 最小的 Sql / 最小的 C#
• 作为 OP 隐式请求从 .Net 调用
• 由于该object_id功能，可能会更快

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