How do I create a function in SQL Server that takes a column of data?
I made the following function in SQL Server 2008 earlier this week that takes two parameters and uses them to select a column of "detailed" records and returns them as one comma separated list of varaar variables. Now that I've thought about it, I would like to use this table and application specific function and make it more general.
I am not good at defining SQL functions since this is my first one. How can I modify this function to accept a single "column" data value so that I can use it in a more general 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 select the column data in my function and then iterate over the results with a cursor to create a comma separated varchar.
How can I change this to accept a single parameter, which is a result set, and then access that result set with a cursor?
a source to share
Others have answered your main question - but let me point out another problem with your function - the awful use of CURSOR!
You can easily rewrite this function to not use a cursor, WHILE loop is nothing like that. It will be much faster and much simpler - 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 it FOR XML PATH('')
- this returns a combined list of columns data
and fixed delimiters ', '
. Add SUBSTRING()
and you're done! As easy as this ..... no dogged-slow CURSOR
, no messie consistency and all this devious code - just one statement and that's it.
a source to share
You can use Table Valued Parameters with SQL Server 2008, which will allow you to pass a TABLE variable as a parameter. Limitations and examples for this are in this linked article.
However, I would also point out that using a cursor can be painful to 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 cursor needed
a source to share
Your question is a little unclear. In your first SQL statement, it looks like you are trying to pass columns to a function, but the WHERE clause is missing. In the second SQL statement, you are passing in a rowset (results from SELECT). Can you provide some sample data and expected results?
Without fully understanding your purpose, you can study changing a parameter as a table variable. Populate a table variable local to the calling code and pass it to the function. You could have done this as a stored procedure, although you would not need a function.
a source to share