sql:function

--查询权限函数--1declare @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 @namesselect  @names--2declare @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)asbegindeclare @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)endgo

用存储过程:

CREATE PROCEDURE proc_Select_BookAdminPermissAssignment(@AdminPermissTypeID varchar(1000))ASdeclare @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