Stored Procedure

• raj-248044

  SSC Eights!

  Points: 838

  More actions

  January 22, 2006 at 8:05 am

  #92856

  SELECT @CLASS_ID = CLASS_ID FROM CLASS_MODULE WHERE YEAR = @YEAR AND SEMESTER = @SEMESTER

  AND (DAY_1_CODE = @CODE AND DAY_1_MODULE = @MODULE_ID)

  OR (DAY_2_CODE = @CODE AND DAY_2_MODULE= @MODULE_ID)

  OR (DAY_3_CODE = @CODE AND DAY_3_MODULE = @MODULE_ID)

  OR (DAY_4_CODE = @CODE AND DAY_4_MODULE = @MODULE_ID)

  OR (DAY_5_CODE = @CODE AND DAY_5_MODULE = @MODULE_ID)

  SELECT CLASS_ROOM FROM CLASS WHERE CLASS_ID = @CLASS_ID

  I have a Stored Procedure as shown above. What it does is it gets the CLASS_ID from the First Select Statement.

  Next it gets the Class_Room using the Class_ID

  The s.p works fine, but i have a problem, since if i run my First Select Statement it returns me 3 IDs.

  But when i execute my S.P i am only get 1 Class Room which is from the 1st ID.

  I need to display all 3 Class Rooms.

  So how do i solve this problem.

  Thanks alot.

• Jo Pattyn

  SSC-Dedicated

  Points: 32350

  More actions

  January 22, 2006 at 9:12 am

  #616293

  SELECT CLASS_ROOM FROM CLASS MyCLASSES WHERE

  EXISTS /*return every matching classroom if there is a related class that fullfills the condition; added CM.CLASS_ID=MyCLASSES.CLASS_ID*/

  (

  SELECT CLASS_ID FROM CLASS_MODULE CM WHERE YEAR = @YEAR AND SEMESTER = @SEMESTER

  AND CM.CLASS_ID=MyCLASSES.CLASS_ID

  AND ((DAY_1_CODE = @CODE AND DAY_1_MODULE = @MODULE_ID)

  OR (DAY_2_CODE = @CODE AND DAY_2_MODULE= @MODULE_ID)

  OR (DAY_3_CODE = @CODE AND DAY_3_MODULE = @MODULE_ID)

  OR (DAY_4_CODE = @CODE AND DAY_4_MODULE = @MODULE_ID)

  OR (DAY_5_CODE = @CODE AND DAY_5_MODULE = @MODULE_ID))

  )

  The problem was that SELECT @.. = only stores one value

• raj-248044

  SSC Eights!

  Points: 838

  More actions

  January 24, 2006 at 2:40 am

  #616601

  Thank you so much, manage to go it working...

  Thanks again, I've been learning alot from this forum.