defusergrouplist.sql

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




create function getUserGroupList(@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_UserGroupList,'^',','),2,len(replace(Js_UserGroupList,'^',','))-2) from Js_User where Js_UserID=@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_GroupName from Js_UserGroup where Js_GroupID=@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

--print dbo.getUserGroupList(2)

--print dbo.KsGetProvideNames('电脑鼠标','3D','个','内江嘉兴电脑')