AdventureWorks2012 DB - how the password validated in sql?

I had seen the below thread which explains how the password validated in C#.

Sql Password validator

I want to do the same in sql stored procedure.

select * from Person.Password

how to validate the password against the values of password salt and password hash? which algorithm its using to encrypt the password?