How do I make a function in SQL Server that accepts a column of data?
- by brandon k
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?
