请教sql中自定义的这个函数为什么出错? ( 积分: 20 )

create&nbsp;function&nbsp;getskfse3(@gysdm&nbsp;varchar(30),@syyf&nbsp;smallint)&nbsp;returns&nbsp;float&nbsp;as<br>begin<br>&nbsp;&nbsp;declare&nbsp;@result&nbsp;float<br>&nbsp;&nbsp;declare&nbsp;@i&nbsp;smallint<br>&nbsp;&nbsp;declare&nbsp;@sqltxt&nbsp;varchar(1500)&nbsp;/*问题出在这里*/<br>&nbsp;&nbsp;set&nbsp;@sqltxt='csye'<br>&nbsp;&nbsp;if&nbsp;@syyf=1&nbsp;select&nbsp;@result=isnull(csye,0)&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm&nbsp;<br>&nbsp;&nbsp;set&nbsp;@i=1<br>&nbsp;&nbsp;if&nbsp;@syyf&gt;1&nbsp;and&nbsp;@syyf&lt;=12<br>&nbsp;&nbsp;begin<br>&nbsp;&nbsp;&nbsp;&nbsp;while&nbsp;@i&lt;=@syyf&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;begin<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;len(@sqltxt)&gt;0&nbsp;set&nbsp;@sqltxt=@sqltxt+'+'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;set&nbsp;@sqltxt=@sqltxt+'zc'+convert(varchar,@i)+'-sr'+convert(varchar,@i)+'-xycj'+convert(varchar,@i)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;set&nbsp;@i=@i+1<br>&nbsp;&nbsp;&nbsp;&nbsp;end&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;@result=(@sqltxt)&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm&nbsp;<br>&nbsp;&nbsp;end<br>&nbsp;return&nbsp;@result<br>end<br><br>执行:<br>select&nbsp;dbo.getskfse3('JDBE-000',12)<br>提示:<br>将数据类型&nbsp;varchar&nbsp;转换为&nbsp;float&nbsp;时出错。<br><br>我在调试的时候,返回&nbsp;varchar&nbsp;(1500)&nbsp;也就是@sqltxt的值内容是:<br>csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3...<br><br>本意是通过select&nbsp;@result=@sqltxt&nbsp;<br>我理想的结果应该是:<br>select&nbsp;@result=csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm<br>得出结果返回的...现在出错,<br>应该是在select&nbsp;@result=@sqltxt&nbsp;这个部分,请教什么原因呢?用什么可以代替让sql执行?

自己顶下,希望各位好汉帮看下~~~

select&nbsp;@result=(@sqltxt)&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm&nbsp;换成&nbsp;exec&nbsp;(select&nbsp;@result=(@sqltxt)&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm&nbsp看看好不好用.

先谢楼上..<br>试了你的说法,无法创建函数了<br>提示:<br>在关键字&nbsp;'select'&nbsp;附近有语法错误。<br>第&nbsp;17&nbsp;行&nbsp;:&nbsp;')'&nbsp;附近有语法错误。

试试执行这个语句能通过吗？<br>select&nbsp;csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm='JDBE-000'<br>请确保csye，str1，zc1，xycj1，str2，zc2，xycj2，str3这些字段是float型的，才能参与运算。

感谢楼上关注,<br>select&nbsp;csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm='JDBE-000'<br>语句可以通过,都是float类型且有值

select&nbsp;@result=(@sqltxt)&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm&nbsp;<br>&nbsp;&nbsp;end<br>&nbsp;return&nbsp;@result<br>&nbsp;这里的@result&nbsp;成了&nbsp;csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3...&nbsp;这些字符串,&nbsp;当然不能转换成float类型的数据了.&nbsp;<br>&nbsp;&nbsp;你需要的select&nbsp;@result=csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3&nbsp;from&nbsp;qmye2006&nbsp;where&nbsp;dm=@gysdm并没有执行.<br>&nbsp;&nbsp;而自定义函数内部又不能执行动态SQL,&nbsp;所以你的问题可能用自定义函数解决不了

楼主，@SqlTxt='csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3'<br>那么，执行select&nbsp;@sqltxt&nbsp;from&nbsp;qmye2006<br>就相当于在执行&nbsp;select&nbsp;'csye+sr1-zc1-xycj1+sr2-zc2-xycj2+sr3'&nbsp;from&nbsp;qmye2006<br>这肯定不行

噢,经过楼上几位剖析,明白了一点<br>那么我现在这个问题就是在自定义函数中无法使用动态sql语句了?<br>受教,再问问有没有其它方法可以搞定呢?

存储过程

自定义函数中无法使用动态sql语句<br>要麼使用存储过程返回值,如果在delphi前台調用,直接在前台組合sql

感谢各位帮助