sql 用户函数举例

由于在项目中用到了sql自定义函数，觉得写得很不错，就把他抄录下来了。

CREATE FUNCTION [dbo].[GetCommaSeparatedIds]
(
    @MetricIdList    VARCHAR(max),
    @delimiter VARCHAR(10) = ','
)
RETURNS
    @tablevalues TABLE
(
    item INT
)
AS
BEGIN
    DECLARE @item VARCHAR(255)
 
  /* Loop over the commadelimited list */
  WHILE (DATALENGTH(@MetricIdList) > 0)
    BEGIN
      IF CHARINDEX(@delimiter,@MetricIdList) > 0
        BEGIN
          SELECT @item =
             SUBSTRING(@MetricIdList,1,(CHARINDEX(@delimiter, @MetricIdList)-1))
          SELECT @MetricIdList =
                SUBSTRING(@MetricIdList,(CHARINDEX(@delimiter, @MetricIdList) +
                DATALENGTH(@delimiter)),DATALENGTH(@MetricIdList))
        END

     ELSE
        BEGIN
          SELECT @item = @MetricIdList
          SELECT @MetricIdList = NULL
        END
 
   -- Insert each item into temp table
    INSERT @tablevalues (item)
    SELECT item = CONVERT(INT, @item)
   END

    RETURN
END

这个函数是根据参入的字符串和分隔符返回一个一个字段的table，字段存储每个分隔符分隔开的字符串，代码的思路是利用while循环，每循环一次截取出一个字符串，直至没有为止，并在组后插入临时表中，返回临时表，

 

 上面的用户函数返回的表，在我这个实际项目中用来做什么呢？下面就是利用返回的table做查询，其中利用到了递归，sqlserver2008新功能。代码如下

 

Create FUNCTION [dbo].[GetAllIdsWithMetricNamePath]
(    
    @MetricIdList varchar(max),
    @delimiter VARCHAR(10) = ','
)
RETURNS TABLE
AS
RETURN
(
    WITH MetricList(MetricIds, pathLength, ChildId, MetricNamePath)
    AS (            
      SELECT CAST(ChildId AS VARCHAR(MAX)), CAST(-1 AS INT),ChildId,
        (SELECT MetricName FROM Metrics WHERE MetricId = ChildId) AS MetricName       
        FROM Relationships
        WHERE ChildId in
        (
            SELECT convert(int,item) FROM dbo.GetCommaSeparatedIds(@MetricIdList, @delimiter)
        )
 
      UNION ALL
 
      SELECT      ML.MetricIds + '/' + CAST(R.ChildId AS VARCHAR(MAX)),
                  pathLength + 1,                  
                  R.ChildId,
                  MetricNamePath + '/' + M.MetricName                
      FROM        MetricList AS ML
      INNER JOIN Relationships AS R ON R.ParentId = ML.ChildId
      INNER JOIN Metrics AS M ON M.MetricId = R.ChildId
      WHERE       MetricIds NOT LIKE '%/' + CAST(R.ChildId AS VARCHAR(MAX)) + '/%'
        AND
        MetricIds NOT LIKE CAST(R.ChildId AS VARCHAR(MAX)) + '/%'
    )
 
    SELECT distinct childid, MetricNamePath FROM MetricList
)