sql:function

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

--查询权限函数 --1 declare @names varchar(3000) set @names='' select @names=@names+isnull(AdminPermissFormName,'')+' , ' from BookAdminPermissTypeList  where AdminPermissTypeID in(8,9,10) set @names=left(@names,len(@names)-1) print @names select  @names   --2 declare @sql nvarchar(4000),@where nvarchar(1000) set @sql='' set @where='8,9,10' set @sql=@sql+'declare  @names varchar(3000)' set @sql=@sql+' set @names=''''' set @sql=@sql+' select @names =@names +isnull(AdminPermissFormName,'''')+'' , ''  from BookAdminPermissTypeList  where AdminPermissTypeID in('+@where+')' set @sql=@sql+' set @names=left(@names,len(@names)-1)' set @sql=@sql+' select @names' print @sql --exec (@sql) exec sp_executesql @sql     --不可以用 if exists(select 1 from sysobjects where name = 'f_GetAdminPermissFormFullName' and xtype = 'FN') drop function f_GetAdminPermissFormFullName go CREATE function f_GetAdminPermissFormFullName (     @AdminPermissTypeID nvarchar(1000) ) returns nvarchar(1000) as begin declare @sql nvarchar(4000),@re nvarchar(1000) set @sql='' --set @where='8,9,10' set @sql=@sql+'declare  @names varchar(3000)' set @sql=@sql+' set @names=''''' set @sql=@sql+' select @names =@names +isnull(AdminPermissFormName,'''')+'' , ''  from BookAdminPermissTypeList  where AdminPermissTypeID in('+@AdminPermissTypeID+')' set @sql=@sql+' set @names=left(@names,len(@names)-1)' set @sql=@sql+' return @names' return exec(@sql) end go

 用存储过程:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE PROCEDURE proc_Select_BookAdminPermissAssignment (     @AdminPermissTypeID varchar(1000) ) AS declare @sql varchar(4000) set @sql='' set @sql=@sql+'declare  @names varchar(3000)' set @sql=@sql+' set @names=''''' set @sql=@sql+' select @names =@names +isnull(AdminPermissFormName,'''')+'' , ''  from BookAdminPermissTypeList  where AdminPermissTypeID in('+@AdminPermissTypeID+')' set @sql=@sql+' set @names=left(@names,len(@names)-1)' set @sql=@sql+' select @names' exec (@sql) GO