ALTER PROCEDURE admin_login
@admin_count nvarchar(50),
@admin_password nvarchar(50),
@admin_last_loginIp nvarchar(50)
AS
declare @admin_id int
SELECT @admin_id = count(admin_id) FROM admin WHERE admin_count = @admin_count and admin_password=dbo.MD5(@admin_password,16)--判断用户名密码匹配
if @admin_id>0 begin
update admin set admin_last_loginIp=@admin_last_loginIp,admin_login_Time=+1 ,admin_last_loginDate=getdate()--更新登录信息
declare @authority varchar(2000),@authority_id varchar(2000),@admin_authority varchar(2000)
select @authority_id=authority_id from admin_group where group_id=(select admin_group_id from admin)--从权限组中取得权限id
select @admin_authority=admin_authority from admin --从管理员表中取得权限id
set @authority=@authority_id+@admin_authority 将两个权限结果加载一起
return @authority
end
else begin
return 0
end
上边是小弟的存储过程,有两个问题请教。第一个就是想得到@authority_id+@admin_authority的值
第二就是如何过滤掉@authority_id+@admin_authority中重复的值如:,1,2,3,3,2,1,结果是,1,2,3,
错是这里错了,但你这个过程让人不知如何回答
ALTER PROCEDURE admin_login
@admin_count nvarchar(50),
@admin_password nvarchar(50),
@admin_last_loginIp nvarchar(50)
AS
declare @admin_id int
SELECT @admin_id = count(admin_id) FROM admin WHERE admin_count = @admin_count and admin_password=dbo.MD5(@admin_password,16)--判断用户名密码匹配
if @admin_id>0 begin
update admin set admin_last_loginIp=@admin_last_loginIp,admin_login_Time=+1 ,admin_last_loginDate=getdate()--更新登录信息
declare @authority varchar(2000),@authority_id varchar(2000),@admin_authority varchar(2000)
select @authority = isnull(@authority+',','')+authority_id from
(
select authority_id from admin_group where group_id=(select admin_group_id from admin where admin_id=@admin_id )--从权限组中取得权限id
union
select admin_authority from admin --从管理员表中取得权限id
) t
return @authority
end
else begin
return 0
end
这里错了,修改为where group_id in (select admin_group_id from admin)