用户自定义函数代替游标进行循环拼接

首先,建立测试表,本例我们采用经典的Customer 1-->n Order结构,如下:

代码

USE  test
GO

IF   EXISTS  ( SELECT   1   FROM  sysobjects  WHERE  name  =   ' Orders ' )
     DROP   TABLE  Orders
    
IF   EXISTS  ( SELECT   1   FROM  sysobjects  WHERE  name  =   ' Customers ' )
     DROP   TABLE  Customers
    
/*
    Customer 1 --> n Order
*/

--  Create Customer Table
CREATE   TABLE  Customers
(
    CustomerId  UNIQUEIDENTIFIER   PRIMARY   KEY ,
    Name  VARCHAR ( 25 ),
)
GO


--  Create Order Table
CREATE   TABLE  Orders
(
    OrderId  UNIQUEIDENTIFIER   PRIMARY   KEY ,
    Name  VARCHAR ( 255 ),
    Customer_Id  UNIQUEIDENTIFIER   FOREIGN   KEY   REFERENCES  Customers(CustomerId)
)
GO

 

 

然后,插入一些测试数据:

 

代码

--  insert test data for customers table
INSERT   INTO  Customers
VALUES ( ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' , ' C1 ' )

INSERT   INTO  Customers
VALUES ( ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' , ' C2 ' )
GO


--  insert test data for orders table
INSERT   INTO  Orders
VALUES ( ' 8EAA07D0-0E04-412A-8A60-3178C9C2E205 ' , ' O1 by C1 ' , ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' )

INSERT   INTO  Orders
VALUES ( ' 78E0CDAB-A3FA-4317-9D4E-D884F91D41A8 ' , ' O2 by C2 ' , ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' )

INSERT   INTO  Orders
VALUES ( ' D08038A9-94FB-4AFF-9E41-F8BEE0FD4384 ' , ' O3 by C1 ' , ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' )

INSERT   INTO  Orders
VALUES ( ' 7487AE08-6313-4DB7-87D7-8F9204F07470 ' , ' O4 by C2 ' , ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' )

INSERT   INTO  Orders
VALUES ( ' 49A76769-D074-4AF3-9707-751D34847E2A ' , ' O5 by C1 ' , ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' )

INSERT   INTO  Orders
VALUES ( ' C5C055EB-49A7-4E4C-82EA-1DB10933C78D ' , ' O6 by C2 ' , ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' )

GO

 

 

建立自定义函数:

 

代码

IF   EXISTS  ( SELECT   1   FROM  sysobjects  WHERE  name  =   ' fnGetCustomerOrders ' )
     DROP   FUNCTION  fnGetCustomerOrders

CREATE   FUNCTION  fnGetCustomerOrders
(
     @CustomerName   VARCHAR ( 25 )
)
     RETURNS   VARCHAR ( MAX )

AS  

BEGIN
     DECLARE   @result   VARCHAR ( MAX )
     SET   @result   =   ''
     SELECT   @result   =   @result   +   ' ,  '   +  o.Name
     from  Customers c  INNER   JOIN  Orders o
             ON  c.CustomerId = o.Customer_Id
     WHERE  c.Name = @CustomerName
     ORDER   by  c.Name, o.Name

     IF   @result   like   ' , % '
         SET   @result   =   right ( @result ,  len ( @result )  -   2 )

     RETURN   @result
END

GO

 

 

测试函数:

 

--  test function
DECLARE   @customer_name   VARCHAR ( 25 )
SET   @customer_name = ' C1 '
SELECT   ' Orders By  '   +   @customer_name   +   ' :  '   +  dbo.fnGetCustomerOrders( @customer_name )

GO

 

 

结果如下:

Orders By C1: O1 by C1, O3 by C1, O5 by C1

 

 

转载于:https://www.cnblogs.com/davidgu/archive/2010/05/17/1737374.html