else if exists(select status from tg_userInfo where (userid=@userid or nickname=@userid) and (password=@userPwd and status=0))
set @message=0;
我输入的条件 完全符合上面的要求 为什么不执行呢 是不 是and 连接写法有误??该 怎么写?
set @message=0;
我输入的条件 完全符合上面的要求 为什么不执行呢 是不 是and 连接写法有误??该 怎么写?
--先确定有结果没
set @message=0
select @message
@userid varchar(50),
@userPwd varchar(50),
@message int output
)
as
begin
if not exists(select 1 from tg_userInfo where userid=@userid or nickname=@userid)
set @message=1;
else if exists(select 1 from tg_userInfo where (userid=@userid or nickname=@userid) and password=@userPwd)
set @message=3;
else if exists(select status from tg_userInfo where (userid=@userid or nickname=@userid) and (password=@userPwd and status=0))
set @message=0;else
set @message=2;
select @message;
end
我执行了下 输出3
else if exists(select 1 from tg_userInfo where (userid=@userid or nickname=@userid) and password=@userPwd)
set @message=3;
else if exists(select status from tg_userInfo where (userid=@userid or nickname=@userid) and (password=@userPwd and status=0))
set @message=0;这两句顺序调换一下,要不然第三个判断是不会输出的,总会被第二条判断过滤掉。
如下:if object_id('tg_userInfo') is not null
drop table tg_userInfo
create table tg_userInfo(
userid varchar(20) primary key,
nickname varchar(20),
password varchar(20),
status int
)
insert into tg_userInfo
select 'aa','aa','aaa',1 union
select 'bb','bb','bbb',0 union
select 'cc','cc','ccc',1 union
select 'dd','dd','ddd',0
select * from tg_userInfo
create PROCEDURE [dbo].[Pro_IgoUserLogin](
@userid varchar(50),
@userPwd varchar(50),
@message int output
)
as
begin
if not exists(select 1 from tg_userInfo where userid=@userid or nickname=@userid)
set @message=1;
else if exists(select status from tg_userInfo where (userid=@userid or nickname=@userid) and (password=@userPwd and status=0))
set @message=0;
else if exists(select 1 from tg_userInfo where (userid=@userid or nickname=@userid) and password=@userPwd)
set @message=3;
else
set @message=2;
select @message;
end
declare @messages int
exec [dbo].[Pro_IgoUserLogin] 'bb','asd',@messages output
print @messages
exec [dbo].[Pro_IgoUserLogin] 'bb','bbb',@messages output
print @messages.
--输出0declare @messages int
exec [dbo].[Pro_IgoUserLogin] 'aa','aaa',@messages output
print @messages
--输出3
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:tg_userInfo
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tg_userInfo')
AND type in (N'U'))
DROP TABLE tg_userInfo
GO---->建表
create table tg_userInfo([userid] int,[nickname] varchar(2),[password] varchar(4),[status] int)
insert tg_userInfo
select 1,'aa','bbbb',0 union all
select 2,'bb','cccc',1
GO--> 查询结果
SELECT * FROM tg_userInfo
--> 删除表格
--DROP TABLE tg_userInfo
--全都正确等于0,输出0
declare @in int
exec Pro_IgoUserLogin '1','bbbb',@in output
select @in--全都正确不等于0,输出3
declare @in int
exec Pro_IgoUserLogin '2','cccc',@in output
select @in--密码错,输出2
declare @in int
exec Pro_IgoUserLogin '1','cccc',@in output
select @in
alter PROCEDURE [dbo].[Pro_IgoUserLogin](
@userid varchar(50),
@userPwd varchar(50),
@message int output
)
as
begin
if not exists(select 1 from tg_userInfo where userid=@userid or nickname =@userid)
set @message=1;
else if exists(select 1 from tg_userInfo where (userid=@userid or nickname=@userid) and password =@userPwd)
begin
set @message=3;
if exists(select status from tg_userInfo where (userid=@userid or nickname=@userid) and (password=@userPwd and status=0))
set @message=0;
end
else
set @message=2;
--select @message;
end