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
EdwardPeterAsked:
Who is Participating?
 
adatheladCommented:
Just remove RETURN from that line:

if pwdcompare(@mypassword, @Password )>0    
select userpropertyvector from user_master where username=@myusername
else
return 0
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi EdwardPeter!

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 :-)
0
 
EdwardPeterAuthor Commented:
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',@mypassword='test'
select @test
0
 
EdwardPeterAuthor Commented:
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
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi EdwardPeter!

Good ...  I'm glad that you've made it.


Regards!
eNTRANCE2002 :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.