学步园

    项目需要，要在查询中，加上后续处理人查询条件，因为后续处理人字段存的是用户ID串，所以想用函数实现功能：根据用户ID串，返回用户姓名串

根据功能，在数据库查询窗口运行测试，sql如下：没有问题，返回用户姓名串

DECLARE @UserIdStr VARCHAR(1024) 
SET @UserIdStr='su20090812001,su20090912002'
DECLARE @sql VARCHAR(2048)
set @sql=N'SELECT  UserName 
FROM Users u WHERE 
u.UserID IN ('''+ REPLACE(@UserIdStr,',',''',''')+''')'
--PRINT @sql
declare @output varchar(8000)
create table #temp(username varchar(20))
INSERT INTO #temp EXEC(@sql)
select  @output=coalesce(@output,'')+ username+','
from #temp
drop table #temp
PRINT substring(@output,1,LEN(@output)-1)

 

结果如下：

(2 行受影响)

kevin,zhangsan

 

现在将上面的sql写成函数，@ UserIdStr是用户ID串，将上面的代码改造成函数，但编译出误。

错误为

消息2772，级别16，状态1，过程GetUserName，第19 行

无法从函数内访问临时表。

消息2772，级别16，状态1，过程GetUserName，第20 行

无法从函数内访问临时表。

消息2772，级别16，状态1，过程GetUserName，第23 行

无法从函数内访问临时表。

消息2772，级别16，状态1，过程GetUserName，第24 行

无法从函数内访问临时表。

 

表明，在函数中，不支持临时表的，将临是表改成表变量，代码如下：

 

 

CREATE FUNCTION GetUserName
(
        @UserIdStr VARCHAR(1024)
)
RETURNS VARCHAR(1024)
AS
BEGIN
    DECLARE @sql VARCHAR(2048)
set @sql=N'SELECT  UserName 
FROM Users u WHERE 
u.UserID IN ('''+ REPLACE(@UserIdStr,',',''',''')+''')'
--PRINT @sql
declare @output varchar(8000)
declare @temp TABLE (username varchar(20))
INSERT INTO @temp EXEC(@sql)
select  @output=coalesce(@output,'')+ username+','
from @temp
--drop table #temp
return substring(@output,1,LEN(@output)-1)

END

 

编译后提示错误如下：

消息443，级别16，状态14，过程GetUserName，第20 行

在函数内的'INSERT EXEC' 中对带副作用的或依赖于时间的运算符的使用无效。

经过上面两次修改，在函数中是可以支持表变量的，不支持exec命令，那怎么能实现上述功能呢，上面的思路主要是想根据用户ID串找到用户姓名列表，然后，再根据coalesce对表列进行处理，将一列一数据转换成字体符串，coalesce的具体用法，请到google下吧。说明有很多，这里不在介绍。

现在整理一下思路：

1.将用户ID串，转换成数据表

2.用户表与步骤1里的表进行多表查询，得到用户姓名表

3.通过coalesce转成字符串

有了思路，就开始动手了，在sql server 中，函数可以返回表变量，正好用上。给这一步的函数起个名称，就叫SplitToTable,代码如下：

 

Create FUNCTION dbo.[SplitToTable](@arr AS VARCHAR(7999))
  RETURNS @t TABLE(pos INT NOT NULL, nvalue VARCHAR(50) NOT NULL)
AS
BEGIN
  DECLARE @end AS INT, @start AS INT, @pos AS INT
  SELECT @arr = @arr + ',', @pos = 1,
    @start = 1, @end = CHARINDEX(',', @arr, @start)
  WHILE @end > 1
  BEGIN
    INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))

    SELECT @pos = @pos + 1,
      @start = @end + 1, @end = CHARINDEX(',', @arr, @start)
  END
  RETURN
END

 

实现了第一步，紧接着，去实现第二步了，这就好实现了，可以两表join一下，或者直接多表查询。因为是要和视图接合，所以还是写成了函数，

代码如下：

 

create FUNCTION dbo.[GetUserName]
(
    @UserIDstr VARCHAR(1024)
)
RETURNS VARCHAR(1024)
AS
BEGIN
    DECLARE @temp TABLE(username VARCHAR(50))
INSERT INTO @temp
SELECT  DISTINCT UserName
 FROM  dbo.Users u,dbo.SplitToTable(@UserIDstr) f WHERE u.UserID=f.[nvalue] –-第二步
 
 DECLARE @output VARCHAR(1024)
 select  @output=coalesce(@output,'')+ username+','
from @temp  --第三步
return substring(@output,1,LEN(@output)-1)

END

 

下面测试下函数，是否返回想要的结果数据，

PRINT dbo.GetUserName('su20090812001,su20090912002')

结果如下：

kevin,zhangsan

完全正确，符合功能的要求。至此，本篇的内容结束，本人对sql 不是很精通，所以想的是实现功能，可能sql 2005/2008 有更好的函数或新功能可以实现，请大家积极留言，共同交流一下。
附上测试表的创建sql语句和数据

/**//*创建表*/
CREATE TABLE [dbo].[Users](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Address] [varchar](260) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
-----------------插入测试数据
INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ('su20090812001','kevin','beijing')
INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912002','zhangsan','tianjin')
INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090701234','wangwu','shanghai')
INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912004','lisi','beijing')
INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912005','jialiu','hebei')
INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912007','qianlong','zhejiang')