Question: Background: I need to write a function in T-SQL on SQL Server 2008 10.0.5869.

Here's the table I'm working on (for the sake of simplicity - I only put in 3 columns here - but I have 10 columns for the actual work):

```
ID | Column1 | Column2 | Column3
1  | 2014-05 | 2015-02 | 2013-04
2  | 2012-09 | 2011-02 | 2013-03

```

ID is varchar and Column(x) are all datetime.

My end goal is to design a function fn\_CompareDate to do something like this:

```
select fn_CompareDate(ID) from table where ID = 1

```

**The query above should return the latest date from Column(x)s which should be 2015-02.**

I used CASE WHEN but it would be almost impossible to use it for 10 columns. Is there another way to achieve the same result?

Answer: 
You can use dynamic sql and INFORMATION\_SCHEMA.COLUMNS. It supposed to work in SQL Server 2008. Try this:

```
CREATE PROCEDURE sp_CompareDate
    @ID int,
    @tableName NVARCHAR(MAX) = 'table2', -- Your table name
    @dbName NVARCHAR(MAX) = 'temp'       -- Your database name
AS
BEGIN
    DECLARE @maxFieldValue DATETIME
    DECLARE @curFieldName NVARCHAR(MAX)
    DECLARE @curFieldValue DATETIME
    DECLARE @sql NVARCHAR(MAX)
    DECLARE fieldCursor CURSOR  FOR 
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @tableName 
            AND TABLE_CATALOG = @dbName AND COLUMN_NAME != 'ID'
    OPEN fieldCursor

    FETCH NEXT FROM fieldCursor INTO @curFieldName
    SET @sql = N'USE [' + @dbName + N'] SELECT @curDate=' + @curFieldName 
        + N' FROM ' + @tableName + N' WHERE ID=' + CAST(@ID AS NVARCHAR) 
    EXEC sp_executesql @sql, N'@curDate DATETIME output', @curFieldValue output;
    SET @maxFieldValue = @curFieldValue

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql = N'USE [' + @dbName + N'] SELECT @curDate=' + @curFieldName 
            + N' FROM ' + @tableName + N' WHERE ID=' + CAST(@ID AS NVARCHAR)
        EXEC sp_executesql @sql, N'@curDate DATETIME output', @curFieldValue output;
        FETCH NEXT FROM fieldCursor INTO @curFieldName

        IF (@maxFieldValue < @curFieldValue) SET @maxFieldValue = @curFieldValue        
    END
    CLOSE fieldCursor;
    DEALLOCATE fieldCursor;
    SELECT @maxFieldValue
END

```

Hope this helps.
I found the 2nd solution from [this question](https://dba.stackexchange.com/questions/21542/what-is-the-most-efficient-way-to-get-the-minimum-of-multiple-columns-on-sql-ser?newreg=dddc7537e3c64fe7a2b9cc65208a9b58) works quite well for me:

Create a function similar to this:

```
select max(col) from 
    (
        select column1 [col] from table where id = @id
        union all
        select column2 from table where id = @id
        union all
        select column3 from table where id = @id
    )

```