Loop Thru Columns

[scostell]

In SQL Server 7 is there a way to Loop Thru every column?

The reason for doing this.

There are like 57 columns. They each hold Dollar values. Sometimes the Dollar values are extended to 4 decimal places and we don't want that.

Is there a way to loop thru the columns and apply the fix within query analyzer? No VB just SQL. Like maybe a column index you can loop thru or something?

Thanks

[DerFarm]

I don't really think so. SQL, Access, Oracle, and the other db's
are restricted to first level indirection in the actual SQL code. First
level indirection is defined as "hardcoded".

You can achieve 2nd level in VB by writing functions that will
produce the code with different column names that have been
passed.

You need pointers of some sort to achieve Nth level which (for all
practical purposes will not include VB).

Essentially speaking, what you are talking about will require at
least some code to create the queries.

[BG]

der_farm is correct. If you dont want to use a seperate app and want to keep it inside SQL Server. "Data Transformation Services" lets you run vbscript in SQL Server. It is nicer for these situations because of the schedueling ability. I use it for something similar to what you are asking and it hasn't bombed out on me once.

this sql will return a recordset with the Column Number and Column Name for your table. hope this helps.


SELECT ORDINAL_POSITION, Column_Name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'your_table_here'