500强企业的测试题.谁会!

[付一笑]

先按要求建表(这个比较简单如下)
create table authentication
(username nvarchar(30),password nvarchar(20),name nvarchar(30),count int)

2.创建存储过程，实现对用户名/密码的验证，要求先判断authentication中是否存在输入用户名
的记录，如果不存在提示‘username is invalid’,如存在则判断密码是否相符，如不符提示
‘password is incorrect’,如果相符提示‘authentication passed’
建议：在你的代码中已定义的输入的变量，顶输出的变量未定义，可改进
由于SQL Server不区分大小写，因此为了区分password大小写，请进一步使用字符转换函数将
password中的每个字符，逐个比较各自的ASCII码是否相同
假设authentication表中有username为siemens，password为Abc123@，count为0，的记录，name字段只是为了提供用户名和真实人名的参照，在验证过程中并无实际用途。

[jinjazz]

沙发

[xluzhong]

学习！up

[sanoul]

8知道，如果是Oracle，我倒是会写

[caibuzhao]

呵

[caibuzhao]

直接提问就行了,赶嘛还把 500强企业 搬出来

[付一笑]

本来就是人家的面试题目...

[付一笑]

CREATE PROCEDURE dbo.ckuser
@username nvarchar(30),@password nvarchar(20),@count int output

AS
if ((select count(*) from authentication where username=@username) =1)

begin
update authentication set count=count+1
where username=@username and password collate Chinese_PRC_CS_AS_WS=@password
end

else

print 'username is invalid'
GO
这个错在那呢.

[付一笑]

if (exists (select * from authentication where username=@username))

[oursaviour]

CREATE PROCEDURE dbo.ckuser
@username nvarchar(30),@password nvarchar(20),@count int output
AS
if exists(select 1 from authentication where username=@username)
begin
print 'username is invalid'
end
else if exists(select 1 from authentication where username=@username and password collate Chinese_PRC_CS_AS_WS=@password)
begin
update authentication set count=count+1 where username=@username
select @count=count from authentication where username=@username
end
else
begin
print 'password is incorrect'
end
GO

[oursaviour]

反了反了

CREATE PROCEDURE dbo.ckuser
@username nvarchar(30),@password nvarchar(20),@count int output
AS
if not exists(select 1 from authentication where username=@username)
begin
print 'username is invalid'
end
else if not exists(select 1 from authentication where username=@username and password collate Chinese_PRC_CS_AS_WS=@password)
begin
update authentication set count=count+1 where username=@username
select @count=count from authentication where username=@username
end
else
begin
print 'password is incorrect'
end
GO