Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: Does anyone have an ForEachColumn Procedure?

    Hi

    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

    Code:
    CREATE PROCEDURE ForEachColumnExcept 
        @TableName varchar(200)
    ,   @ExceptionList varchar(8000) = '' -- the few ones to exclude
    ,   @DelimterInExceptionlist varchar(10)  = ','
    AS
    ...
    END
    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.

    Or even:

    Code:
    CREATE PROCEDURE ForEachColumnInList 
        @TableName varchar(200)
    ,   @ColumnList varchar(8000) -- all the ones to include
    ,   @DelimterInColumnList varchar(10) = ','
    AS
    ...
    END

    Cheers

    Drio

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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

    Code:
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •