[SQL] sybase while loop assistance

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

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 > 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


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).

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.

Can anyone help me get this loop working? Or tell me what I am doing wrong?

Or just hard-coding all the supv_clas_cd and union the result the better solution in class than trying to loop all this?

My original idea was/is DECLARE @district varchar(2), @numSchools int SELECT @district = 'MS', @numSchools = 1

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


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.

A sample of the data looks something like

t_supv_class|t_Schools

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

and from this sample, I would want

Acidlog
Alexander
Amazing
Bambleweeny
Bang

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.

For the record, I have only read-only access to the database, so I cannot create a procedure (which was another thought I had).

Continue reading...