How to store a query result into a variable? SQL Server 2008 I have two tables and I want to store the result query of my stored procedure
into a variable.

UserTable:

UserID int primary key identity
CredentialsID int
AccountType nvarchar(15)
ModifiedDate date


CredentialsTable:

Credentials ID int primary key identity
Password


Stored procedure:

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

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


Executing the select statement will return a credentialsID which I want to 
store in the @ID parameter. So I can use it on my other statements to be made.
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?