想用SQL CLR做如下的存储过程,但是发现报错:
{由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。}
而且如果数据从case 1通过时就是正确的,从case 2通过时错误
并且在case 2中的myCommand.CommandText后如果是个select语句就是正确的,但是运行存储过程就通不过,而且单独测试存储过程是正确的,好像问题是出在record的发送上,求教!
[SqlProcedure]
public static void procClr(SqlXml sx)
{
XPathDocument doc = new XPathDocument(sx.CreateReader());
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select(@"/NewDataSet/StandardDT");SqlDataRecord record =new SqlDataRecord(new SqlMetaData("i_num", SqlDbType.Int));SqlPipe sp = SqlContext.Pipe;
sp.SendResultsStart(record); while (i.MoveNext())
{
//----解析XML数据----
int inum=Convert.ToInt32(i.Current.Evaluate("string(i_num[1]/text())"));
//-----处理数据------
int flag=Function(inum);//自定义函数,无误
switch(flag)
{
case 1:
{
record.SetValues(1);
break;
}
case 2:
{
using (SqlConnection myConnection = new SqlConnection("context connection=true"))
{
SqlCommand myCommand = myConnection.CreateCommand();
myConnection.Open();
myCommand.CommandText = "EXEC [sp_proc] @inum = 2"; //[sp_proc]:返回整型的存储过程,测试无误
int back= myCommand.ExecuteScalar();
record.SetValues(back);
}
break;
}
}
sp.SendResultsRow(record);//出错点
}
sp.SendResultsEnd();
}
{由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。}
而且如果数据从case 1通过时就是正确的,从case 2通过时错误
并且在case 2中的myCommand.CommandText后如果是个select语句就是正确的,但是运行存储过程就通不过,而且单独测试存储过程是正确的,好像问题是出在record的发送上,求教!
[SqlProcedure]
public static void procClr(SqlXml sx)
{
XPathDocument doc = new XPathDocument(sx.CreateReader());
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select(@"/NewDataSet/StandardDT");SqlDataRecord record =new SqlDataRecord(new SqlMetaData("i_num", SqlDbType.Int));SqlPipe sp = SqlContext.Pipe;
sp.SendResultsStart(record); while (i.MoveNext())
{
//----解析XML数据----
int inum=Convert.ToInt32(i.Current.Evaluate("string(i_num[1]/text())"));
//-----处理数据------
int flag=Function(inum);//自定义函数,无误
switch(flag)
{
case 1:
{
record.SetValues(1);
break;
}
case 2:
{
using (SqlConnection myConnection = new SqlConnection("context connection=true"))
{
SqlCommand myCommand = myConnection.CreateCommand();
myConnection.Open();
myCommand.CommandText = "EXEC [sp_proc] @inum = 2"; //[sp_proc]:返回整型的存储过程,测试无误
int back= myCommand.ExecuteScalar();
record.SetValues(back);
}
break;
}
}
sp.SendResultsRow(record);//出错点
}
sp.SendResultsEnd();
}
小F,一直没搞懂,剪剪是指剪嫂吗?
奥! 了解了!!! 谢谢狼哥答疑哈!