Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create a stored procedure with a select inside 1

Status
Not open for further replies.

free47

Programmer
Jun 9, 2008
10
US
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 :)

Code:
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
 
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

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

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

Nice to know it worked for you.

Thanks

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top