How to store a query result into a variable? SQL Server 2008
If you are looking to set the ID variable parameter, you need to specify it as an OUTPUT parameter, and set it's value in your query:

Create procedure addFaculty 
@ID int OUTPUT
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin 
        Insert into CredentialsTable values ('iamafaculty');

        Select @ID = CredentialsTable.CredentialsID 
        from CredentialsTable 
        where CredentialsTable.Password = 'iamafaculty';
    end
end


Is that what you are asking?