导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

问个非常简单的查询问题

zoujiaming 2008-01-14 02:59:22
我有一个表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
...全文
60 点赞 收藏 14
写回复
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
tim_spac 2008-01-14
测试没问题啊。
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
回复
pt1314917 2008-01-14

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

回复
kk19840210 2008-01-14
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 行受影响)
回复
kk19840210 2008-01-14
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
回复
zoujiaming 2008-01-14
to pt1314917

用你写的存储过程没查询到结果
回复
pt1314917 2008-01-14

我的呢?
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 2008-01-14
to tim_spac

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

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


结果倒显示出来了,可是显示的也太多了
回复
tim_spac 2008-01-14
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
回复
liangCK 2008-01-14
哦..原来如此..
楼上两位就可以了..
jf
回复
pt1314917 2008-01-14

上面创建存储过程时敲错了。应该是proc。不好意思哈。
--调用存储过程:
exec GetCategoryAuthority @Category='Protocol',@RoleName='Admin'
回复
zoujiaming 2008-01-14
to liangCK

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

列名。我想选择自己想要的列名,得到所对应的true或false
回复
tim_spac 2008-01-14
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
回复
pt1314917 2008-01-14

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
回复
liangCK 2008-01-14
Protocol和Link都是所对应的版块
个是@Category(输入的版块),一个是@RoleName(输入的角色)
@Category='Protocol',@RoleName='Admin'

Protocol列是true or false
那你传入@Category='Protocol'
是什么意思?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告