[haynest]

Hello everybody,

I am trying to create a stored procedure to accomplish 2 things. I've commented my code below to help you understand my logic.

/* @Promo='somechars' */

SELECT @Aid=AgentId  /*assign the selected column value to @Aid */
FROM ATABLE /*from this table */
WHERE PromoNumber=@Promo  /*where col PromoNumber='somechars'

SELECT AName As AgentName,
            Office,
            Email
FROM ANOTHERTABLE WHERE Id=@Aid /*Get all info for AgentID from first select*/

/*Then start from the first SELECT again to display the next AgentID
     that the WHERE specifies*/

However, my code will only ouput a single AgentId. There should be 4 AgentId's that meet the conditions of the WHERE clause in the first select statement.

Any Ideas? Thanks! I hope my explanation was good enough. I'm here so reply if you have any questions.

[eclipsed4utoo]

SELECT 
     AnotherTable.AName AS 'AgentName'
     , AnotherTable.Office
     , AnotherTable.Email
FROM AnotherTable JOIN ATable 
          ON AnotherTable.Id = ATable.AgentID
WHERE ATable.PromoNumber = @Promo

[xerxes333]

The reason you are getting only one result back is due to your first SQL statement.

SELECT @Aid=AgentId  /*assign the selected column value to @Aid */
FROM ATABLE /*from this table */
WHERE PromoNumber=@Promo  /*where col PromoNumber='somechars'

The above SQL assigns the value of AgentId to the variable @Aid for each record that matches @Promo and each time it finds a match @Aid gets updated with the new AgentId value. So if you run a simple select statement like

SELECT @AgentId FROM ATABLE WHERE PromoNumber=@Promo

your results would look something like

-- AgentId ---
111
222
333

but because you are assigning the table result to a variable (@Aid) each time a new record is found @Aid gets the new value. Hence only one agent is being "found".

eclipsed4utoo has provided you with a solution to your problem. I just felt it was important for you to know WHY you were getting the results you were.