我尝试下面的代码为cheking SP是alredy存在与否.如果不存在我正在创造..
但每次显示sp都没有创建…..但我的数据库已经有了这个sp.
让我知道我在哪里做错了.
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
}
解决方法
尝试：
if exists(select * from sys.objects where type = 'p' and name = '<procedure name>' )
你也可以用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; } } } }
