none
求教:SQL2005 内联表值函数执行报错问题. RRS feed

  • 问题

  • 环境:SQL2005 WIN2003 SERVER

    创建内联表值函数SPLIT,用于按指定字符将字符串分隔代码如下:

    CREATE FUNCTION [dbo].[Split](@Expression VARCHAR(MAX),@Delimiter char(1))
     RETURNS Table
    AS
     
     RETURN(
     --补充前缀和后缀
     WITH cte1 AS(SELECT case when left(@Expression,len(@Delimiter))=@Delimiter then '' else @Delimiter end +@Expression+
          case when right(@Expression ,len(@Delimiter))=@Delimiter  then '' else @Delimiter end AS Expression),
     --构建自然数表
     L0 AS(SELECT 1 AS n UNION ALL SELECT 1),
     L1 AS(SELECT 1 AS n FROM L0 a,L0 b),
     L2 AS(SELECT 1 AS n FROM L1 a,L1 b),
     L3 AS(SELECT 1 AS n FROM L2 a,L2 b),
     L4 AS(SELECT 1 AS n FROM L3 a,L3 b),
     L5 AS(SELECT 1 AS n FROM L4 a,L4 b),
     cte2 AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L5)
     /*SELECT SUBSTRING(@Delimiter+@Expression+@Delimiter,n+1,
     CHARINDEX(@Delimiter,@Delimiter+@Expression+@Delimiter,n+1) -n-1  ) AS list
     FROM   cte2 n
     WHERE SUBSTRING(@Delimiter+@Expression+@Delimiter,n,1)=@Delimiter
     AND n.n<LEN(@Delimiter+@Expression+@Delimiter)
     AND isnull(@Expression,'')<>''
     AND ISNULL(@Delimiter,'')<>''*/
     --分隔字符串
     SELECT SUBSTRING(b.Expression,n+1,CHARINDEX(@Delimiter,b.Expression,n+1) -n-1  ) AS list
     FROM   cte2 n,cte1 b
     WHERE SUBSTRING(b.Expression,n,1)=@Delimiter
     AND n.n<LEN(b.Expression)
     AND isnull(@Expression,'')<>''
     AND ISNULL(@Delimiter,'')<>''
     )

    测试代码如下:

    SELECT list from dbo.[Split]('/1/','/') --执行正常

    SELECT list from dbo.[Split]('/1/','/') WHERE list='1' --执行异常消息 536,级别 16,状态 5,第 1 行传递到 SUBSTRING 函数的长度参数无效。

    从上面的测试发现,为此函数添加过滤条件时总是报错.但是我将该函数改成多语表值函数,用表变量返回结果却可以正常执行过滤条件.不知内联表值函数与多语表值函数为何有此区别?百思不得其解,望求教.

    2011年10月2日 7:48

答案

  • 而且我发现另一个问题,这个函数的执行计划异常庞大.当用此函数与其他表做Apply运算时,非常慢.但是换成该函数的多语表值函数版本却非常快. 很好奇内联表值函数的执行机理是怎样的.
    • 已标记为答案 三断笛1 2012年10月17日 2:40
    2011年10月2日 8:11

全部回复

  • 而且我发现另一个问题,这个函数的执行计划异常庞大.当用此函数与其他表做Apply运算时,非常慢.但是换成该函数的多语表值函数版本却非常快. 很好奇内联表值函数的执行机理是怎样的.
    • 已标记为答案 三断笛1 2012年10月17日 2:40
    2011年10月2日 8:11
  • 关注中


    it's time to start living the life you are imagined.
    2011年10月7日 9:15