问个非常简单的查询问题

[zoujiaming]

我有一个表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

[liangCK]

Protocol和Link都是所对应的版块
个是@Category(输入的版块)，一个是@RoleName(输入的角色)
@Category='Protocol',@RoleName='Admin'

Protocol列是true or false
那你传入@Category='Protocol'
是什么意思?

[pt1314917]

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

[tim_spac]

create procedure proc_GetCategoryAuthority 
  @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

[zoujiaming]

to liangCK

Protocol列是true or false
那你传入@Category='Protocol'
是什么意思?

列名。我想选择自己想要的列名，得到所对应的true或false

[pt1314917]

上面创建存储过程时敲错了。应该是proc。不好意思哈。
--调用存储过程：
exec GetCategoryAuthority @Category='Protocol',@RoleName='Admin'

[liangCK]

哦..原来如此..
楼上两位就可以了..
jf

[tim_spac]

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

[zoujiaming]

to tim_spac

调用你的存储过程为何显示

消息 217，级别 16，状态 1，第 1 行
超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。


结果倒显示出来了，可是显示的也太多了

[pt1314917]

我的呢？
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

[zoujiaming]

to pt1314917

用你写的存储过程没查询到结果

[kk19840210]

create table Authority(id int identity(1,1),RoleName varchar(10),Protocol varchar(10),link varchar(10))

insert into Authority values('admin','true','true')

insert into Authority values('user','false','true')


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)
end

exec GetCategoryAuthority 'link,Protocol','admin,user'


link       Protocol
---------- ----------
true       true
true       false

[kk19840210]

alter proc GetCategoryAuthority (@Category varchar(100),@RoleName varchar(100))
as
begin
		declare @sql varchar(1000)
		select @sql=''
		select @sql='select RoleName,'+@Category+' from Authority where charindex('',''+RoleName+'','','','+@RoleName+','')>0'
		exec(@sql)
end

exec GetCategoryAuthority 'link,Protocol','admin,user'--可以填入多个板块与角色 中间用 ，号隔开



RoleName   link       Protocol
---------- ---------- ----------
admin      true       true
user       true       false

(2 行受影响)

[pt1314917]

不好意思。刚才没试。掉了个符号
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

[tim_spac]

测试没问题啊。

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
go

exec proc_GetCategoryAuthority 'Protocol','Admin'
go
drop table Authority
drop procedure proc_GetCategoryAuthority
go

CategoryRight
True