Best How To :

A very simple aproach is to use a MD5 hash.

public class MD5
{
    public static string Hash(string message)
    {
        // step 1, calculate MD5 hash from input
        System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
        byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(message);
        byte[] hash = md5.ComputeHash(inputBytes);

        // step 2, convert byte array to hex string
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < hash.Length; i++)
        {
            sb.Append(hash[i].ToString("X2"));
        }
        return sb.ToString().ToUpper();
    }
}

Then in your application

You say

var password = MD5.hash(passwordField);

And store that in the DB.

When validating the password you just say something like

db.Account.Where(w => w.email == emailField && w.password == MD5.hash(passwordField)

to see if you have a matching record.

As @zulq said there are better systems something that has a salt etc, however for basic password hashing as you requested, this will work.

However if you wish to do all this in a stored procedure. You can use the following HASHBYTES function in SQL

HASHBYTES('md5', 'your password')

So same again when calling the stored procedure, you pass it the plain text password it hashes and stores

When validating you pass a stored procedure the username / password it validates and returns true or false or a row.