我有一个表Authority
ID RoleName Protocol Link
1 Admin true true
2 User false true
RoleName是角色
Protocol和Link都是所对应的版块
现在我想设置两个变量,一个是@Category(输入的版块),一个是@RoleName(输入的角色)
我想通过一个查询或存储过程反回所需要版块的bool值
比如存储过程名为GetCategoryAuthority,参数为@Category='Protocol',@RoleName='Admin'返回true
ID RoleName Protocol Link
1 Admin true true
2 User false true
RoleName是角色
Protocol和Link都是所对应的版块
现在我想设置两个变量,一个是@Category(输入的版块),一个是@RoleName(输入的角色)
我想通过一个查询或存储过程反回所需要版块的bool值
比如存储过程名为GetCategoryAuthority,参数为@Category='Protocol',@RoleName='Admin'返回true
个是@Category(输入的版块),一个是@RoleName(输入的角色)
@Category='Protocol',@RoleName='Admin'Protocol列是true or false
那你传入@Category='Protocol'
是什么意思?
create ptoc GetCategoryAuthority
@Category varchar(50),
@RoleName varchar(50)
as
declare @sql nvarchar(8000)
declare @bool varchar(50)
set @sql='select @bl='+@Category +' from Authority where RoleName=''+@RoleName+'''
exec sp_executesql @sql,N'@bl varchar(50) output',@bool output
select @bool
@Category varchar(32) -- ='Protocol'
,@RoleName varchar(32) -- ='Admin'
as begin
declare @sql varchar(128)
set @sql = 'select '+@Category+' from Authority where RoleName = '''+@RoleName+''''
exec (@sql)
end
那你传入@Category='Protocol'
是什么意思?列名。我想选择自己想要的列名,得到所对应的true或false
上面创建存储过程时敲错了。应该是proc。不好意思哈。
--调用存储过程:
exec GetCategoryAuthority @Category='Protocol',@RoleName='Admin'
@Category varchar(32) -- ='Protocol'
,@RoleName varchar(32) -- ='Admin'
as begin
declare @sql varchar(128)
set @sql = 'select CategoryRight='+@Category+' from Authority where RoleName = '''+@RoleName+''''
exec (@sql)
end
超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。
结果倒显示出来了,可是显示的也太多了
我的呢?
create proc GetCategoryAuthority
@Category varchar(50),
@RoleName varchar(50)
as
declare @sql nvarchar(8000)
declare @bool varchar(50)
set @sql='select @bl='+@Category +' from Authority where RoleName=''+@RoleName+'''
exec sp_executesql @sql,N'@bl varchar(50) output',@bool output
select @bool
create proc GetCategoryAuthority (@Category varchar(100),@RoleName varchar(100))
as
begin
declare @sql varchar(1000)
select @sql=''
select @sql='select '+@Category+' from Authority where charindex('',''+RoleName+'','','','+@RoleName+','')>0'
exec(@sql)
endexec GetCategoryAuthority 'link,Protocol','admin,user'
link Protocol
---------- ----------
true true
true false
as
begin
declare @sql varchar(1000)
select @sql=''
select @sql='select RoleName,'+@Category+' from Authority where charindex('',''+RoleName+'','','','+@RoleName+','')>0'
exec(@sql)
endexec GetCategoryAuthority 'link,Protocol','admin,user'--可以填入多个板块与角色 中间用 ,号隔开RoleName link Protocol
---------- ---------- ----------
admin true true
user true false(2 行受影响)
不好意思。刚才没试。掉了个符号
alter proc GetCategoryAuthority
@Category varchar(50),
@RoleName varchar(50)
as
declare @sql nvarchar(4000)
declare @bool varchar(50)
set @sql='select @bl='+@Category +' from Authority where RoleName='''+@RoleName+''''
exec sp_executesql @sql,N'@bl varchar(50) output',@bool output
select @bool
create table Authority (ID int identity(1,1), RoleName varchar(16), Protocol char(5), Link char(5))
insert into Authority values ('Admin','True','True')
insert into Authority values ('User','False','True')
go
create procedure proc_GetCategoryAuthority
@Category varchar(32) -- ='Protocol'
,@RoleName varchar(32) -- ='Admin'
as begin
declare @sql varchar(128)
set @sql = 'select CategoryRight='+@Category+' from Authority where RoleName = '''+@RoleName+''''
exec (@sql)
end
goexec proc_GetCategoryAuthority 'Protocol','Admin'
go
drop table Authority
drop procedure proc_GetCategoryAuthority
goCategoryRight
True