Iterate over a rowset, get a field which matches parameter, and then another field from that row

Question

I have a stored procedure which has to get a password from my Users table.

I am using a Username as a parameter. What is the best way to get the row where the Username field's contents match the Username parameter, and then the password (as this is the Username/password pair for one user).

Cursors are one way to iterate over a rowset but these aren't desirable.

I am currently reading a few TSQL books, but haven't come across this sort of problem. Any good recommendations for TSQL books? I am on Sql Server 2005.

Answer 1

It sounds like you just need a standard SELECT query:

SELECT username_column, password_column
FROM your_table
WHERE username_column = @usernameparam

(Or am I misunderstanding the question?)

Answer 2

SELECT password FROM table WHERE username = @username

Answer 3

Hi,

Assuming that your users table has the columns UserName and Password then a SELECT will work just fine.

SELECT username, password FROM Users WHERE username = @username

However, storing passwords in this manner might not be the best idea security wise.

Answer 4

get a password from my Users table

This is your first problem: NEVER store real passwords in a database. Look into the HashBytes() function and use that for password matching. If you think you need to keep a real password around, perhaps to allow users to recover lost passwords, go talk to the folks over at reddit.

way to iterate over a rowset

This is your second problem. You're thinking in terms of iterating over the rows yourself. This is called imperative programming. While imperative code works great for your normal client code, it's backwards when you're working with a database. Instead, you want to start using declarative programming and think in terms a sets: you write some code that declares to the database the set of records you need returned:

SELECT [password] /*shudder*/ FROM [table] WHERE [username] = @username