如何传“查询条件”到function里面定义的CURSOR中执行？

Question

改存储过程这种直接传递条件语句的处理需要动态sql语句来完成. 而用户定义函数是不支持的.

Answer 1

CREATE function xzc.fn_getContent_Balance
(@s_p_no int = NULL,
          @sql_where varchar = NULL)
returns @fn_getContent_Balance TABLE
(ID varchar(8000),
 SupplyPlanNo int,
 ItemCode varchar(20),
 Vendor varchar(20)
)
as
  BEGIN
    
   /*Outer CURSOR*/
   exec('DECLARE getItemBalance CURSOR SCROLL FOR
select ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
 from (
select min(ID) as ID,ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
 from Balance where SupplyPlanNo='+@s_p_no+' 
 group by ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
          ) a  order by a.ID desc')

Answer 2

CREATE proc xzc.fn_getContent_Balance
(@s_p_no int = NULL,
          @sql_where varchar = NULL)
as
begin
set nocount on
declare @fn_getContent_Balance TABLE
(ID varchar(8000),
 SupplyPlanNo int,
 ItemCode varchar(20),
 Vendor varchar(20)
)
    
   /*Outer CURSOR*/
declare @@s_p_no varchar(10)
set @@s_p_no=@s_p_no
exec('
   DECLARE getItemBalance  CURSOR SCROLL global FOR
select ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
 from (
select min(ID) as ID,ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
 from Balance where SupplyPlanNo='+@@s_p_no+' and '+@sql_where+'  -- *******
 group by ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
          ) a  order by a.ID desc')
end

Answer 3

惨了，老大说函数不行。那就：
CREATE proc xzc.fn_getContent_Balance
(@s_p_no int = NULL,
          @sql_where varchar = NULL)
as
declare  @fn_getContent_Balance TABLE
(ID varchar(8000),
 SupplyPlanNo int,
 ItemCode varchar(20),
 Vendor varchar(20)
)
  BEGIN
    
   /*Outer CURSOR*/
   exec('DECLARE getItemBalance CURSOR SCROLL FOR
select ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
 from (
select min(ID) as ID,ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
 from Balance where SupplyPlanNo='+@s_p_no+' 
 group by ItemCode,ItemName,ItemSpec,UnitName,PlanPrice,Part,Vendor,VendorName
          ) a  order by a.ID desc')

Answer 4

1.动态拼SQL语句需要用到exec，而函数中不能用exec，所以只能改存储过程
2.表定义记录最大长度为8060，你定义的超过了该值

Answer 5

多谢，zjcxc(邹建)，我原来一直想是如何利用function来实现，刚才测试了一下，发现好用，但是好像效率差了不少，相同条件：函数2000毫秒左右，存储过程需要2900左右，因为我后面的语句还要进行一定的归类处理，所以必须要插入到自定义的表中，如果用procedure，还得插入然后select，影响了一定的速度阿，呵呵TO: pbsql(风云)  多谢提醒，因为数据量不会有那么大超过8600，我测试用的，呵呵TO: 631799(杭州工人) 多谢关注，可能你没太看清说明，