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<=(SELEC T COUNT(*) FROM INFORMATION_SCH EMA.COLUMNS WHERE
TABLE_NAME='MyT able')
BEGIN
SELECT @colname=COLUMN _NAME FROM INFORMATION_SCH EMA.COLUMNS WHERE
TABLE_NAME='MyT able' AND ORDINAL_POSITIO N=@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