Create a stored procedure with a select inside 1

[free47]

I am saving a password in VarBinary in my database. Now I am trying to authentic if the username and password typed in was correct. I am trying to do it in the where clause and take the password the user enters and check it against the binary saved password. It won’t let me do a sub select like I have in the code below, is there some other way I can save the binary password to @varpassword to compare it? Any rearranging of my code would be much appreciated

CREATE PROCEDURE [dbo].[UserIDGet1] 
	-- Add the parameters for the stored procedure here
	@username nvarchar(20), 
	@password nvarchar(20)
AS
BEGIN
	
    DECLARE @varPassword varbinary(255)

	SELECT id, username, rsiid
	From CustLogin
	where @username = username AND pwdcompare(@password,(SELECT @varPassword = [Password] FROM [User] where UserName = @username), 0) = 1;

END

 

[memdiscount]

Hi;

This join should work, try and let us know. Assuming you have relationship in two tables with id columns if not then change join to username.

*************************

CREATE PROCEDURE [dbo].[UserIDGet1]
-- Add the parameters for the stored procedure here
@username nvarchar(20),
@password nvarchar(20)
AS
BEGIN

SELECT id, username, rsiid
FROM CustLogin A
INNER JOIN [User] B ON A.id = B.id
WHERE @username = username
AND pwdcompare(@password, [Password],0) = 1

END

*****************

Thanks

 

[free47]

That worked perfectly! how is it always the easiest part of the query gives me a hard time!

THANKS!

 

[memdiscount]

Becuase the hardest part must have resolved by you easily........

Nice to know it worked for you.

Thanks