[b]LOOPING THRU TABLE FIELDS[/b]

[kissarmi]

I need to keep a total of each field within a tabel. Is there a way with an SQL statement in my SP to loop through the fields in each record without being forced to name each field specifically. This is a large table, and I'm trying to get away from something like "Field1Tot = Field1Tot + Field1." Maybe some kind of For/Next loop?

Thanks for any help.

 

[SQLSister]

?

Looping is bad especially with a large table.

What's wrong with sum(field1)?


Questions about posting. See faq183-874

 

[mrdenny]

you can use the imformation schema views to generate some dynamix SQL to get the list of column names.

Are you trying to update a totals field for each record? I'm assuming that you are.

declare @Column_Name as varchar(100)
declare @CMD varchar(1000)
set @CMD = '('
declare cur CURSOR for select * from INFORMATION_SCHEMA.columns where table_name = '1003Assets'
open cur
fetch next from cur into @COLUMN_NAME
while @@FETCH_STATUS = 0
BEGIN
	set @CMD = @CMD + @COLUMN_NAME + '+'
	fetch next from cur into @COLUMN_NAME
END
close cur
deallocate cur

set @CMD = left(@CMD, len(@CMD)-1) + ')'
set @CMD = 'update table set TotalColumn=' + @CMD
exec (@CMD)

Is something like this what your looking for?

Denny

--Anything is possible. All it takes is a little research. (Me)