defguserlist.sql

本系统是一个报表分析查询系统

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getGUserList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getGUserList]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





create function getGUserList(@GroupID int)
 returns varchar(8000)
 as
 begin
 
  declare @UserName varchar(128) 
  declare @UserNameList varchar(8000)
  declare @UserIDList varchar(8000)
  declare @UserIDStr varchar(128)
  declare @UserIDInt int

  set @UserNameList=''
  select @UserIDList=substring(replace(Js_UserList,'^',','),2,len(replace(Js_UserList,'^',','))-2) from Js_UserGroup where Js_GroupID=@GroupID
  
  --定义游标
  declare UserIDs cursor for
   select *  from dbo.DefSplit(@UserIDList,',')

  open UserIDs

  fetch next from UserIDs into @UserIDStr
  while @@fetch_status=0
   begin
    set @UserIDInt=convert(int,@UserIDStr)
    set @UserName=''
    select @UserName=js_UserName from js_user where js_userid=@UserIDInt
    if @UserName<>''
     begin
      set @UserNameList=@UserNameList+@UserName+']['   
     end
    fetch next from UserIDs into @UserIDStr
   end   
  if(len(ltrim(rtrim(@UserNameList)))>1)
   set @UserNameList='['+substring(@UserNameList,1,len(@UserNameList)-1)
  return @UserNameList
 end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO