EdwardPeter
asked on
return value from store procedure with verifying encrypted apssword
Hi,
Please kindly assist how can we verify the password and then return the userPopertyvector value?
else if verified failed return 0
Thanks.
Server: Msg 156, Level 15, State 1, Procedure user_master_verify, Line 10
Incorrect syntax near the keyword 'RETURN'.
Server: Msg 170, Level 15, State 1, Procedure user_master_verify, Line 10
Line 10: Incorrect syntax near '>'.
Server: Msg 156, Level 15, State 1, Procedure user_master_verify, Line 12
Incorrect syntax near the keyword 'else'.
create proc dbo.user_master_verify
@myUsername as varchar(50),
@myPassword as varchar(50)
as
DECLARE @Password VARBINARY(255)
SELECT @Password = pass
FROM user_master
WHERE Username = @myusername
if RETURN pwdcompare(@mypassword, @Password )>0 <------------
select userpropertyvector from user_master where username=@myusername
else
return 0
Go
Please kindly assist how can we verify the password and then return the userPopertyvector value?
else if verified failed return 0
Thanks.
Server: Msg 156, Level 15, State 1, Procedure user_master_verify, Line 10
Incorrect syntax near the keyword 'RETURN'.
Server: Msg 170, Level 15, State 1, Procedure user_master_verify, Line 10
Line 10: Incorrect syntax near '>'.
Server: Msg 156, Level 15, State 1, Procedure user_master_verify, Line 12
Incorrect syntax near the keyword 'else'.
create proc dbo.user_master_verify
@myUsername as varchar(50),
@myPassword as varchar(50)
as
DECLARE @Password VARBINARY(255)
SELECT @Password = pass
FROM user_master
WHERE Username = @myusername
if RETURN pwdcompare(@mypassword, @Password )>0 <------------
select userpropertyvector from user_master where username=@myusername
else
return 0
Go
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How come it returns two tables in the QA?
one with 1 and the other with 0
declare @test char(10)
exec @test = user_master_verify @myusername='test',@mypass
select @test
one with 1 and the other with 0
declare @test char(10)
exec @test = user_master_verify @myusername='test',@mypass
select @test
ASKER
Got it.
create proc dbo.SP_user_master_verify
@myUsername as varchar(50),
@myPassword as varchar(50)
as
DECLARE @Password VARBINARY(255), @myuserpropertyvector char(1)
SELECT @Password = pass, @myuserpropertyvector = userpropertyvector
FROM user_master
WHERE Username = @myusername
RETURN CASE WHEN pwdcompare(@mypassword, @Password ) >0 THEN @myuserpropertyvector ELSE 0 END
Go
create proc dbo.SP_user_master_verify
@myUsername as varchar(50),
@myPassword as varchar(50)
as
DECLARE @Password VARBINARY(255), @myuserpropertyvector char(1)
SELECT @Password = pass, @myuserpropertyvector = userpropertyvector
FROM user_master
WHERE Username = @myusername
RETURN CASE WHEN pwdcompare(@mypassword, @Password ) >0 THEN @myuserpropertyvector ELSE 0 END
Go
Hi EdwardPeter!
Good ... I'm glad that you've made it.
Regards!
eNTRANCE2002 :-)
Good ... I'm glad that you've made it.
Regards!
eNTRANCE2002 :-)
You can simplify your procedure in this way :
create proc dbo.user_master_verify
@myUsername as varchar(50),
@myPassword as varchar(50)
as
DECLARE @Password VARBINARY(255), @myuserpropertyvector [data type]
SELECT @Password = pass, @myuserpropertyvector = userpropertyvector
FROM user_master
WHERE Username = @myusername
if pwdcompare(@mypassword, @Password )>0
select @myuserpropertyvector
else
return 0
Go
Hope this helps you. Just try it.
Goodluck!
eNTRANCE2002 :-)