SQL Server T-SQL User-Defined Function of the Week

Create a Column List for Dynamic SQL

Volume 1 Number 43   September 9, 2003

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

In an interesting article on SQLTeam.com Bill Graziano writes
about an interview question that had been posed to one of the
sites readers:

How can you retrieve the first N number of fields using sql 
statement without using field names?

Bill's solution is in the article that he wrote about it here:
http://www.sqlteam.com/item.asp?ItemID=2971

His solution to the problem is this script:

/------- Start copying below this line -------------------------\
set nocount on

Declare @TableName varchar(128), @ColumnList varchar(1000)
Declare @SQL varchar(1000)
set @TableName = 'authors'

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
FROM INFORMATION_SCHEMA.Columns
WHERE Ordinal_position <= 10
and table_name = @TableName

Set @SQL = 'Select ' + @ColumnList + ' from ' + @TableName

Exec (@SQL)
GO
\-------Stop copying above this line ---------------------------/

As I looked at it I realized that I had a similar solution
available in a UDF, udf_Tbl_ColumnList.  My original UDF
didn't limit the number of columns.  But that's an easy 
enchantment that's now included.

First, here's the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_Tbl_ColumnList (
    
    @TableName sysname -- Name of the table
  , @Prefix sysname = NULL -- Prefix to prepend to each column
     -- NULL indicates no prefix. Brackets can be used around
     -- prefix. Don't include the period that separates the 
     -- prefix from the column name. Generally used for an alias.
  , @Owner sysname = NULL -- Table owner name, NULL for none
  , @NumColumns int = 0 -- Number of columns, 0 for all
)  RETURNS nvarchar(4000) -- Column names quoted.
/*
* Creates a quoted list of the columns in a table, suitable for
* creation of a dynamic SQL statement. Depends on 
* INFORMATION_SCHEMA.COLUMNS.
*
* Related information: See article on a similar non-UDF solution
*                  http://www.sqlteam.com/item.asp?ItemID=2971
*
* Example:
DECLARE @TableName sysname
SELECT TOP 1 @TableName = TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
select @TableName as [Table]
    , dbo.udf_Tbl_ColumnList(@TableName, '[alias]', NULL, 3)
* 
* History:
* When          Who     Description
* ------------- ------- ----------------------------------------
* 2003-08-13    ASN     Added @NumColumns parameter.
* 2003-05-10    ASN     Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Vol 1 #43 9/9/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @Result nvarchar(4000) -- the result 

SET @Result = '' -- So it's not a null string

SELECT @Result = 
         -- After 1st column, add a comma 
         CASE WHEN LEN(@Result) > 0 
              THEN @Result + ', ' 
              ELSE '' 
              END

         -- Add the prefix if it's specified in the call
       + CASE WHEN @Prefix IS NOT NULL and LEN(@Prefix) > 0
              THEN @Prefix + '.'
              ELSE ''
              END
        -- Quote the column name
      + fn_replquotename (COLUMN_NAME)

    FROM INFORMATION_SCHEMA.Columns
    WHERE TABLE_NAME = @TableName
      AND (@Owner IS NULL or @Owner = TABLE_SCHEMA)
      AND (@NumColumns IS NULL or @NumColumns<1 
              or ORDINAL_POSITION <= @NumColumns)
    ORDER BY Ordinal_Position

RETURN @Result
END
GO

GRANT EXEC ON [dbo].[udf_Tbl_ColumnList] TO [PUBLIC]
GO
\-------Stop copying above this line ---------------------------/

The typical use for this function is when creating dynamic SQL.
So if you've created the UDF in PUBS, you could select the 
first 4 columns of the Authors table with dynamic SQL.  First,
let's look at the sql statement that's going to query the
table:

/------- Start copying below this line -------------------------\
DECLARE @SQL as nvarchar(4000)
SET @SQL = 'SELECT ' 
         + dbo.udf_Tbl_ColumnList ('authors', '[a]', default, 4)
         + ' FROM Authors a '

PRINT @SQL
GO
\-------Stop copying above this line ---------------------------/
(Results - Reformatted with a little wrapping)
SELECT [a].[au_id], [a].[au_lname], [a].[au_fname], [a].[phone] 
     FROM Authors a 

I've used an alias to illustrate that feature.  Since there's
only one table in the query it isn't necessary.

Next, let's create the SQL statement again and feed it to EXEC:
/------- Start copying below this line -------------------------\
DECLARE @SQL as nvarchar(4000)
SET @SQL = 'SELECT ' 
         + dbo.udf_Tbl_ColumnList ('authors', '[a]', default, 4)
         + ' FROM Authors a '

EXEC (@SQL)
GO
\-------Stop copying above this line ---------------------------/
(Results - abridged)
au_id       au_lname         au_fname             phone       
----------- ---------------- -------------------- ------------
172-32-1176 White            Johnson              408 496-7223
213-46-8915 Green            Marjorie             415 986-7020
238-95-7766 Carson           Cheryl               415 548-7723
267-41-2394 O'Leary          Michael              408 286-2428
274-80-9391 Straight         Dean                 415 834-2919
341-22-1782 Smith            Meander              913 843-0462
409-56-7008 Bennet           Abraham              415 658-9932
427-17-2319 Dull             Ann                  415 836-7128
472-27-2349 Gringlesby       Burt                 707 938-6445
.....

There are a couple of interesting things about the function:

1) It allows for an optional alias, which might be useful.
2) It allows for specification of an owner. I always have all
   tables owned by dbo, but there are times when it could come
   into play.

In addition to these features there are a few differences in the
way udf_Tbl_ColumnList is written from the solution in the 
article:

1) I used CASE instead of COALESCE, but this is mostly style.
2) I used an ORDER BY clause.  Information_SCHEMA always seems
   to come out in the correct order but I don't think it's 
   guaranteed.
3) I used the undocumented system UDF fn_replquotename. I could
   have just added the leading and trailing brackets.  I think 
   fn_replquotename is used because the UDF was written while 
   I wrote the chapter in my book about undocumented system UDFs.
   
While I'm at it, did I mention the book?

SQL Server User-Defined Functions
by Andrew Novick

Will be published this fall by Wordware! It's finished and into
the publisher.  I'll let you know when it comes out.

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick