不允许现有用户使用存储过程? [英] does not allow the existing user using stored procedure?
问题描述
亲爱的,
使用存储过程不允许现有用户
我正在编写代码下面工作正常,但用户名正常工作,电子邮件无法正常工作我想要两个都不允许现有的电子邮件和用户名
这里有什么错误请回复我
ALTER PROCEDURE [dbo]。[sp_userinformation]
@ UserName varchar ( 50 ) ,
@密码 varchar ( 50 ),
@ FirstName varchar ( 50 ),
@ LastName varchar ( 50 ),
@ Email varchar ( 50 ),
@ PhoneNo varchar ( 50 ),
@ Location varchar ( 50 ),
@ Created_By varchar ( 50 ),
@ ERROR VARCHAR ( 100 )OUT
AS
BEGIN
- 添加SET NOCOUNT ON以防止来自 $的额外结果集b $ b - 干扰SELECT语句。
SET NOCOUNT ON ;
- - 如果用户不存在则检查条件是否存在如果用户不存在则返回不同的消息exists返回不同的消息
IF NOT EXISTS ( SELECT * FROM User_Information WHERE UserName = @ UserName AND 电子邮件= @电子邮件)
BEGIN
INSERT INTO User_Information
(
用户名,
[密码],
FirstName,
姓氏,
电子邮件,
PhoneNo,
位置,
Created_By
)
VALUES
(
@ UserName ,
@ Password ,
@ FirstName ,
@ LastName ,
@ Email ,
@ PhoneNo ,
@ Location ,
@ Created_By
)
- 如果用户成功注册我正在将此消息作为输出参数退出
SET @ ERROR = @ UserName + ' 已成功注册'
END
ELSE
BEGIN
- 如果用户已存在我将此消息作为输出参数返回
SET @ ERROR = @ UserName + ' 已存在'
SET @ ERROR = @Email + ' 已存在'
END
结束
这里有什么错误回复我
你应该用OR
条件而不是AND
所以更改下面的查询
IF NOT EXISTS ( SELECT * FROM User_Information WHERE UserName = @ UserNameAND或电子邮件= @电子邮件)
Gotcha
问题@ ELSE部分。您最后分配@Email +'Already Exists'
,以便存储过程返回最后一行值。
ELSE
BEGIN
- 如果用户已存在我将此消息作为输出参数返回
SET @ ERROR = @ UserName + ' 已存在'
SET @ ERROR = @Email + ' 已存在' //你< span class =code-string>' 重新赋值给@Error,SP取最新值&忽略前一行
END
现在你必须改变逻辑。获取UserName&的值表和电子邮件中的电子邮件参数写下面的逻辑。 警告 :这只是虚拟脚本,所以检查语法&其他的东西。
ELSE
BEGIN
- 如果用户已存在,我将此消息作为输出参数返回
IF @ UserName <> ' ' AND @ Email <> ' '
BEGIN
SET @ ERROR = ' 两者' + @ UserName + ' < span class =code-string>,' + @ Email + ' 已经存在'
END
ELSE IF @ UserName <> ' '
BEGIN
SET @ ERROR = @ UserName + ' 已经存在'
END
ELSE IF @ Email <> ' '
BEGIN
SET @ ERROR = @Email + ' 已经存在'
END
END
Dear all,
using stored procedure does not allow the existing user
am writing code below is working properly but username working properly and email not working i want both does not allow the existing email and username
what mistake here please reply me
ALTER PROCEDURE [dbo].[sp_userinformation]
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(50),
@PhoneNo varchar(50),
@Location varchar(50),
@Created_By varchar(50),
@ERROR VARCHAR(100) OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
---Checking Condition if User exists or not if user not exists returns different message if exists returns different message
IF NOT EXISTS(SELECT * FROM User_Information WHERE UserName=@UserName AND Email=@Email)
BEGIN
INSERT INTO User_Information
(
UserName,
[Password],
FirstName,
LastName,
Email,
PhoneNo,
Location,
Created_By
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName,
@Email,
@PhoneNo,
@Location,
@Created_By
)
--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR=@UserName+' Registered Successfully'
END
ELSE
BEGIN
--If User already Exists i am returning this Message as Output Parameter
SET @ERROR=@UserName + ' Already Exists'
SET @ERROR=@Email + 'Already Exists'
END
END
here what mistake plese reply me
You should useOR
condition instead ofAND
so change the query like below
IF NOT EXISTS(SELECT * FROM User_Information WHERE UserName=@UserNameANDOR Email=@Email)
Gotcha
The problem @ ELSE part. You're assigning@Email + 'Already Exists'
at last so the stored procedure returns the last line value.
ELSE BEGIN --If User already Exists i am returning this Message as Output Parameter SET @ERROR=@UserName + ' Already Exists' SET @ERROR=@Email + 'Already Exists'//You're assigning value to @Error, SP takes the latest value & ignores the previous line END
Now you have to change the logic. Get values for UserName & Email parameters from table & write logic like below. Warning: It's just dummy script so check syntax & other things.
ELSE BEGIN --If User already Exists i am returning this Message as Output Parameter IF @UserName <> '' AND @Email <> '' BEGIN SET @ERROR = 'Both ' + @UserName + ',' + @Email + ' Already Exists' END ELSE IF @UserName <> '' BEGIN SET @ERROR=@UserName + ' Already Exists' END ELSE IF @Email <> '' BEGIN SET @ERROR=@Email + 'Already Exists' END END
这篇关于不允许现有用户使用存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!