SQL cursors: This was a funny one

This was a funny one from msdn forums:

Is it possible to order the column names in alphabetical order in Sql Server?

This is a problem that in my view elightens how much you can’t do with SQL.
First thought is to use CLR.
Since I never use SQL OPEN CURSOR and FETCH the obvious took some time.

I am sure there is a more elegant solution using XML in some way, but I think XML gets overly complex at times, so I avoid using it if there are more practical solutions. Even if they don’t always look that elegant.

Here is my solution:

-- SQL to output table with columns sorted

CREATE PROCEDURE pTableWithSortedFields(@tableName nvarchar(MAX))
AS
DECLARE @sql as nvarchar(MAX)
SET @sql = 'SELECT '

DECLARE temporary_cursor CURSOR FOR
select name from sys.all_columns
where object_id = OBJECT_ID(@tableName)
order by name

DECLARE @field as nvarchar(MAX)
OPEN temporary_cursor

FETCH NEXT FROM temporary_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sql = @sql + '[' + @field + ']'
     FETCH NEXT FROM temporary_cursor INTO @field
     IF @@FETCH_STATUS = 0
          SET @sql = @sql + ','
END

CLOSE temporary_cursor
DEALLOCATE temporary_cursor

SET @sql = @sql + ' FROM ' + @tableName
print @sql
EXEC (@sql)

GO

pTableWithSortedFields 'sometable'

-- SQL to output table with columns sorted

Well, answering posts (when I get the time) can be a learning experience. I am sure I will use cursors for something again soon, another tool is somewhat sharpened