Lets say you wanna have some ids from the rows of a table as comma seperated value, how would you do that. Using a simple user defined function this can be real easy task.

Sample code for the user defined function:

Here is a sample code to create the Scaler-Valued user defined function that gets the colors ids for a productId passed as variable in comma separated form.

CREATE FUNCTION [dbo].[GetColorIdsCSV](@productId int)
RETURNS varchar(1000)
AS
BEGIN

DECLARE @p_str VARCHAR(1000)
SET @p_str = ”

SELECT @p_str = @p_str + ‘,’ + CAST(colorId AS VARCHAR(10))
FROM ProductColors Where productId=@productId)
–if results exist there would be a , at the beginning. remove it.
IF LEN(@p_str) > 0
SET @p_str = SUBSTRING(@p_str, 2, LEN(@p_str)-1)

RETURN @p_str

END

To use this function:

Simply call it from the select statement. As an example:

Select productId,dbo.GetColorIdsCSV(58) as colorIds from Products

Note that you have to use the database owner (in the example above dbo) to call the scaler-valued user defined function.

Example output:

55,298,528