title: sybase while loop assistance
body: <p>I have a basic understanding of SQL and Sybase, this is the first time I have tried to loop something. I tried several suggestions here, and put together the following</p>
  
  <pre><code> DECLARE @district varchar(2), @numSchools int, @sc_cd varchar(2)
   SELECT @district = 'MS', @numSchools = 1
  
   SET rowcount 0
   SELECT supv_clas_cd INTO #MyTemp
       FROM t_supv_class
       WHERE eff_dt IS NULL
  
   DECLARE @rowsaf INT
   SELECT @rowsaf = @@rowcount FROM #MyTemp
  
   WHILE @rowsaf &gt; 1
   BEGIN
       SET rowcount 1
       SELECT @sc_cd = supv_clas_cd FROM #MyTemp
       SET rowcount 0
       DELETE #MyTemp WHERE @sc_cd = supv_clas_cd
       SELECT @rowsaf = @@rowcount FROM #MyTemp
  
       SELECT TOP @numSchools
           s.nm_tx AS 'School Name', @sc_cd AS 'Supv Class'
          FROM t_Schools s
          LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
          WHERE supv_clas_cd = @sc_cd
          AND s.csrv_cd = @district
   END 
   SET rowcount 0
  </code></pre>
  
  <p>What I am attempting to do is avoid a large number of UNIONs where only the "supv_clas_cd" changes.
  Basically, return a set number of school names for each given supv_clas (which can change as the table is editted).</p>
  
  <p>The temp table #MyTemp has, on average, 40 rows within it.
  Not all combinations of "supv_clas_cd" and "crsv_cd" have values, but TRY/CATCH does not seem to hope.</p>
  
  <p>Can anyone help me get this loop working? Or tell me what I am doing wrong?</p>
  
  <p>Or just hard-coding all the supv_clas_cd and union the result the better solution in class than trying to loop all this?</p>
  
  <p>My original idea was/is
      DECLARE @district varchar(2), @numSchools int
      SELECT @district = 'MS', @numSchools = 1</p>
  
  <pre><code>SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'CC'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'CH'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'CO'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'CT'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DA'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DB'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DI'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DJ'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DM'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DS'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'DV'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'FB'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'FO'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'LC'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'LH'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'LL'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'LN'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'LT'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'MC'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'MH'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'ML'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'MN'
      AND s.csrv_cd = @district
  
  UNION
  
  SELECT TOP @numSchools
      s.nm_tx
      FROM t_Schools s
      LEFT OUTER JOIN t_supv_class sc ON sc.supv_clas_cd = s.supv_clas_cd
      WHERE supv_clas_cd = 'MT'
      AND s.csrv_cd = @district
  </code></pre>
  
  <p>This assumes that "WHERE supv_clas_cd" does not change, and if I need to change the SELECT statement I have to do it in each and every location.</p>
  
  <p>A sample of the data looks something like 
  <PRE>
  t_supv_class|t_Schools<br>
  supv_clas_cd|nm_tx            supv_clas_cd
  CC          |Acidlog    DB
  CH          |Alexander  FB
  CO          |Amazing     CT
  CT          |Arthur  FB
  DA          |Babel   CT
  DB          |Babica FB
  DI          |Bambleweeny    DB
  DJ          |Bang   CH
  DM          |Beeblebrox CT
  DS          |Beneath    CH
  DV          |Betelgeuse DB
  FB          |Blagulon   CT
  FO          |Blaster    CH
  LC          |Both   DB
  LH          |Brain  CH
  LL          |Bugblatter CH
  LN          |By CH
  LT          |Clare  DB
  MC          |Cos    CH
  MH          |Could  CH
  ML          |Damogran   CT
  MN          |Dark   DB
  MT          |Deep   CH
  </PRE></p>
  
  <p>and from this sample, I would want
  <PRE>
  Acidlog
  Alexander
  Amazing
  Bambleweeny
  Bang
  </PRE></p>
  
  <p>I was thinking of looping so centralize the SELECT statement for maintenance and reusable, as well as not hard-coding all the values of t_supv_clas.supv_clas_cd.</p>
  
  <p>For the record, I have only read-only access to the database, so I cannot create a procedure (which was another thought I had).</p>