Get Columns From SP!

[Arpan]

An ASP application needs to display DISTINCT records of all the columns
existing in a SQL Server DB table in different drop-down lists. The
column names & the no. of columns aren't known while coding. The no. of
drop-downs depend on the no. of columns i.e. if the DB table has 10
columns, there should be 10 drop-downs. This is the stored procedure I
have written to do this:

----------------------------------------
CREATE PROCEDURE GetRecords
AS
DECLARE
@ordpos int,
@colname sysname,
@sql varchar(8000)

SET @ordpos=1

WHILE @ordpos<=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME='MyTable')
BEGIN
SELECT @colname=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME='MyTable' AND ORDINAL_POSITION=@ordpos
SET @sql='SELECT DISTINCT(' + @colname + ') FROM MyTable ORDER BY ' +
@colname
EXEC(@sql)
SET @ordpos=@ordpos+1
END
----------------------------------------

As such, the above stored procedure works fine when executed in the
Query Analyzer but the problem is in populating the drop-downs in the
ASP page. Since the stored procedure generates the column names
dynamically, I don't know the column names while coding the different
drop-downs.

How do I populate the drop-downs? Note that I want the name of the
individual drop-downs to be the name of the column.

Thanks,


Arpan