How do I make a function in SQL Server that accepts a column of data?

Question

I made the following function in SQL Server 2008 earlier this week that takes two parameters and uses them to select a column of "detail" records and returns them as a single varchar list of comma separated values. Now that I get to thinking about it, I would like to take this table and application-specific function and make it more generic.

I am not well-versed in defining SQL functions, as this is my first. How can I change this function to accept a single "column" worth of data, so that I can use it in a more generic way?

Instead of calling:

SELECT ejc_concatFormDetails(formuid, categoryName)

I would like to make it work like:

SELECT concatColumnValues(SELECT someColumn FROM SomeTable)

Here is my function definition:

FUNCTION [DNet].[ejc_concatFormDetails](@formuid AS int, @category as VARCHAR(75))
RETURNS VARCHAR(1000) AS
BEGIN
 DECLARE @returnData VARCHAR(1000)
 DECLARE @currentData VARCHAR(75)
 DECLARE dataCursor CURSOR FAST_FORWARD FOR
  SELECT data FROM DNet.ejc_FormDetails WHERE formuid = @formuid AND category = @category

 SET @returnData = ''

 OPEN dataCursor

 FETCH NEXT FROM dataCursor INTO @currentData
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
  SET @returnData = @returnData + ', ' + @currentData
  FETCH NEXT FROM dataCursor INTO @currentData
 END

 CLOSE dataCursor
 DEALLOCATE dataCursor

 RETURN SUBSTRING(@returnData,3,1000)
END

As you can see, I am selecting the column data within my function and then looping over the results with a cursor to build my comma separated varchar.

How can I alter this to accept a single parameter that is a result set and then access that result set with a cursor?

Answer 1

You can use a table variable:

CREATE FUNCTION MyFunction(
    @Data AS TABLE (
        Column1 int,
        Column2 nvarchar(50),
        Column3 datetime
    )
)
RETURNS NVARCHAR(MAX)
AS BEGIN
    /* here you can do what you want */
END

Answer 2

You can use Table Valued Parameters as of SQL Server 2008, which would allow you to pass a TABLE variable in as a parameter. The limitations and examples for this are all in that linked article.

However, I'd also point out that using a cursor could well be painful for performance. You don't need to use a cursor, as you can do it all in 1 SELECT statement:

SELECT @MyCSVString = COALESCE(@MyCSVString + ', ', '') + data 
FROM DNet.ejc_FormDetails 
WHERE formuid = @formuid AND category = @category

No need for a cursor

Answer 3

Your question is a bit unclear. In your first SQL statement it looks like you're trying to pass columns to the function, but there is no WHERE clause. In the second SQL statement you're passing a collection of rows (results from a SELECT). Can you supply some sample data and expected outcome?

Without fully understanding your goal, you could look into changing the parameter to be a table variable. Fill a table variable local to the calling code and pass that into the fuction. You could do that as a stored procedure though and wouldn't need a function.

Answer 4

Others have answered your main question - but let me point out another problem with your function - the terrible use of a CURSOR!

You can easily rewrite this function to use no cursor, no WHILE loop - nothing like that. It'll be tons faster, and a lot easier, too - much less code:

FUNCTION DNet.ejc_concatFormDetails
            (@formuid AS int, @category as VARCHAR(75))
RETURNS VARCHAR(1000) 
AS
    RETURN 
      SUBSTRING(
        (SELECT ', ' + data
         FROM DNet.ejc_FormDetails 
         WHERE formuid = @formuid AND category = @category
         FOR XML PATH('')
        ), 3, 1000)

The trick is to use the FOR XML PATH('') - this returns a concatenated list of your data columns and your fixed ', ' delimiters. Add a SUBSTRING() on that and you're done! As easy as that..... no dogged-slow CURSOR, no messie concatenation and all that gooey code - just one statement and that's all there is.