"Arpan" wrote:

> My application retrieves records from a SQL Server 7.0 DB table for
> which I am using a stored procedure but while coding, I am not aware of
> the no. of columns existing in that DB table. Neither am I aware of the
> column names while coding. Also more columns can get added to the table
> in the future.
>
> What I want to do is retrieve the DISTINCT records from each column &
> populate them in drop-down lists that will be finally displayed to the
> user. For each column, there will be 1 drop-down list which will be
> listing the DISTINCT records of that column. For e.g. if there are,
> say, 15 columns, there will be 15 drop-down lists i.e. one drop-down
> for each column. Had I known the column names while coding, the
> following queries would have sufficed:
>
> SELECT DISTINCT(Col1) FROM MyTable ORDER BY Col1
> SELECT DISTINCT(Col2) FROM MyTable ORDER BY Col2
> SELECT DISTINCT(Col3) FROM MyTable ORDER BY Col3
> .............
> .............
>
> but since the column names are not known, the above won't work in my
> case. I have to get the column names first. To do so, I am using the
> INFORMATION_SCHEMA.COLUMNS view in my stored procedure to first get the
> column names & then looping through the columns to get the DISTINCT
> records from each of the columns. This is code snippet in my stored
> procedure:
>
> ----------------------------------------
> CREATE PROCEDURE GetRecords
> AS
> DECLARE
>     @sql varchar(8000)
>
> WHILE (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
> TABLE_NAME='MyTable') IS NOT NULL
> BEGIN
>     SET @sql='SELECT DISTINCT(COLUMN_NAME) FROM MyTable WHERE COLUMN_NAME
> IS NOT NULL ORDER BY COLUMN_NAME'
> END
> EXEC (@sql)
> ----------------------------------------
>
> But when the above is executed in QA, as expected, it throws the
> "Subquery returned more than 1 value. This is not permitted......"
> error.
>
> So how do I get the column names & then retrieve the DISTINCT records
> from each column?
>
> Thanks,
>
> Arpan
>
>

"Alejandro Mesa" wrote:

> Try,
>
>
> > WHILE (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
> > TABLE_NAME='MyTable') IS NOT NULL
>
> declare @ordinal_position int
> declare @cn sysname
>
> set @ordinal_position = 1
>
> while @ordinal_position <= (select count(*) from information_schema.columns
> where table_schema = 'dbo' and table_name = 'MyTable')
>    begin
>    select
>       @cn = column_name
>    from
>       information_schema.columns
>    where
>       table_schema = 'dbo' and table_name = MyTable'
>       and ordinal_position = @ordinal_position
>
>    set @sql = 'SELECT DISTINCT(' + quotename(@cn) + ') FROM MyTable ORDER BY
> ' + quotename(@cn)
>
>    exec sp_executesql @sql
>
>    set @ordinal_postion = @ordinal_position + 1
>   
>    end
> ...
>
>
> AMB
>
> "Arpan" wrote:
>
> > My application retrieves records from a SQL Server 7.0 DB table for
> > which I am using a stored procedure but while coding, I am not aware of
> > the no. of columns existing in that DB table. Neither am I aware of the
> > column names while coding. Also more columns can get added to the table
> > in the future.
> >
> > What I want to do is retrieve the DISTINCT records from each column &
> > populate them in drop-down lists that will be finally displayed to the
> > user. For each column, there will be 1 drop-down list which will be
> > listing the DISTINCT records of that column. For e.g. if there are,
> > say, 15 columns, there will be 15 drop-down lists i.e. one drop-down
> > for each column. Had I known the column names while coding, the
> > following queries would have sufficed:
> >
> > SELECT DISTINCT(Col1) FROM MyTable ORDER BY Col1
> > SELECT DISTINCT(Col2) FROM MyTable ORDER BY Col2
> > SELECT DISTINCT(Col3) FROM MyTable ORDER BY Col3
> > .............
> > .............
> >
> > but since the column names are not known, the above won't work in my
> > case. I have to get the column names first. To do so, I am using the
> > INFORMATION_SCHEMA.COLUMNS view in my stored procedure to first get the
> > column names & then looping through the columns to get the DISTINCT
> > records from each of the columns. This is code snippet in my stored
> > procedure:
> >
> > ----------------------------------------
> > CREATE PROCEDURE GetRecords
> > AS
> > DECLARE
> >     @sql varchar(8000)
> >
> > WHILE (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
> > TABLE_NAME='MyTable') IS NOT NULL
> > BEGIN
> >     SET @sql='SELECT DISTINCT(COLUMN_NAME) FROM MyTable WHERE COLUMN_NAME
> > IS NOT NULL ORDER BY COLUMN_NAME'
> > END
> > EXEC (@sql)
> > ----------------------------------------
> >
> > But when the above is executed in QA, as expected, it throws the
> > "Subquery returned more than 1 value. This is not permitted......"
> > error.
> >
> > So how do I get the column names & then retrieve the DISTINCT records
> > from each column?
> >
> > Thanks,
> >
> > Arpan
> >
> >

"Arpan" <arpan***@hotmail.com> wrote in message
news:1128025951.296851.198910@g43g2000cwa.googlegroups.com...
> My application retrieves records from a SQL Server 7.0 DB table for
> which I am using a stored procedure but while coding, I am not aware of
> the no. of columns existing in that DB table. Neither am I aware of the
> column names while coding. Also more columns can get added to the table
> in the future.
>
> What I want to do is retrieve the DISTINCT records from each column &
> populate them in drop-down lists that will be finally displayed to the
> user. For each column, there will be 1 drop-down list which will be
> listing the DISTINCT records of that column. For e.g. if there are,
> say, 15 columns, there will be 15 drop-down lists i.e. one drop-down
> for each column. Had I known the column names while coding, the
> following queries would have sufficed:
>
> SELECT DISTINCT(Col1) FROM MyTable ORDER BY Col1
> SELECT DISTINCT(Col2) FROM MyTable ORDER BY Col2
> SELECT DISTINCT(Col3) FROM MyTable ORDER BY Col3
> ............
> ............
>
> but since the column names are not known, the above won't work in my
> case. I have to get the column names first. To do so, I am using the
> INFORMATION_SCHEMA.COLUMNS view in my stored procedure to first get the
> column names & then looping through the columns to get the DISTINCT
> records from each of the columns. This is code snippet in my stored
> procedure:
>
> ----------------------------------------
> CREATE PROCEDURE GetRecords
> AS
> DECLARE
> @sql varchar(8000)
>
> WHILE (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
> TABLE_NAME='MyTable') IS NOT NULL
> BEGIN
> SET @sql='SELECT DISTINCT(COLUMN_NAME) FROM MyTable WHERE COLUMN_NAME
> IS NOT NULL ORDER BY COLUMN_NAME'
> END
> EXEC (@sql)
> ----------------------------------------
>
> But when the above is executed in QA, as expected, it throws the
> "Subquery returned more than 1 value. This is not permitted......"
> error.
>
> So how do I get the column names & then retrieve the DISTINCT records
> from each column?
>
> Thanks,
>
> Arpan
>

"Arpan" <arpan***@hotmail.com> wrote in message
news:1128040629.262128.211750@g43g2000cwa.googlegroups.com...
> Thanks, Tibor, for your suggestion.
>
> Yeah....you are right.....the DB table has indeed been designed very
> poorly by someone who doesn't know anything about designing DB tables &
> DB programming but due to paucity of time, he wants me to adhere to
> that DB only. Actually the DB table has been imported from a MS-Excel
> file.
>
> BTW, is adding columns to a DB table, dynamically or manually, after
> that table has been in use for a considerable period of time a bad
> practice?
>
> Why doesn't EXEC (@sql) in your procedure generate any resultset?
>
> Thanks once again,
>
> Regards,
>
> Arpan
>

>I am sorry Tibor.....there was a typo in the stored procedure due to
>which no resultset was generated. The typo was in
>
>FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
>TABLE_NAME='MyTable' ORDER BY ORDINAL_POSITION
>
>line. I had coded it as
>
>FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
>TABLE_NAME='MyTable ORDER BY ORDINAL_POSITION'
>
>but stangely enough, neither the procedure generated any error while
>compiling nor were any errors generated when the procedure was executed
>in QA. Why so? Is it because the compiler presumed the table name to be
>'MyTable ORDER BY ORDINAL_POSITION'? If so, then shouldn't the compiler
>have generated the "Invalid object name" error since no such table
>exists in the DB?