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;
}
}
}
}
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();
}
I found this on MSDN
select * from sys.objects where type_desc = 'SQL_STORED_PROCEDURE' AND name = 'Sql_PersonInsert'