Unanswered: Does anyone have an ForEachColumn Procedure?
Does anyone have an ForEachColumn Procedure?
(a bit like the MSforeachdb and MSforeachtable sps)
This should be quite generic for anyone working with real life data coming in from other systems.
I need to fill out the rows with missing values in historic records from the previous current record.
(sometimes even from a previous historic record)
And sometimes the current record doesn't have data in the field, so I shouldn't get the data from current -1.
Can solve most of the complexities of the filling out part.
But it needs to be applied for (most of) my 57 columns,and then there is even some differently formatted data just beyond the horizon, so maybe I should try to get a more generic applicable solution.
Did anyone try a ForEachColumnExcept procedure
CREATE PROCEDURE ForEachColumnExcept
, @ExceptionList varchar(8000) = '' -- the few ones to exclude
, @DelimterInExceptionlist varchar(10) = ','
the difficulty here is getting the columns from the databse's data-dictionary
)haven't done that before, but I think I'll find that out.
CREATE PROCEDURE ForEachColumnInList
, @ColumnList varchar(8000) -- all the ones to include
, @DelimterInColumnList varchar(10) = ','
not sure what u finally want to do with the column list, this will however generate a column list & data type based on params u declared
set @ExceptionList = ','+ @ExceptionList + ','
select Column_name,Data_Type from information_schema.columns
where table_name=@TableName and column_name not in (case when charindex(','+column_name+',',@ExceptionList) > 0 then column_name else '' end)