SQL Server 2008 – How to Store a Query Result into a Variable

sql serverstored-procedures

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.

Best Answer

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?

Related Question