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

问个非常简单的查询问题

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

...全文

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+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告