Error creating UDF in SQL Server 2005

Question

Hi,

I'm trying to create a UDF in SQL Server 2005 Express as below:

CREATE FUNCTION [CombineValues] ()
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @CuisineList VARCHAR(8000);

RETURN 
    (
    SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + 
        CAST(Cuisine AS varchar(20))
    FROM Cuisines
    )
END

Cuisines has the structure:

CuisineID INT PK,
Cuisine VARCHAR(20)

When I try to create the function as above, I get an error:

Msg 102, Level 15, State 1, Procedure CombineValues, Line 10
Incorrect syntax near '='.

What am I doing wrong?

Answer 1

try changing SELECT to SET and then end your function by SELECT (ing) your @CuisineList

Answer 2

Hojou, your suggestion didn't work, but something similar did:

CREATE FUNCTION [CombineValues] ()
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @CuisineList VARCHAR(8000);

SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + CAST(Cuisine AS varchar(20)) FROM Cuisines;

RETURN 
(
SELECT @CuisineList
)
END

I would like to mark this as the answer, but since I am the one who asked this question, I'm not sure this is appropriate? Any suggestions? Please feel feel to comment.

Answer 3

This answer is from the original poster, Wild Thing. Please do not vote it up or down.

CREATE FUNCTION [CombineValues] ()
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @CuisineList VARCHAR(8000);

SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + CAST(Cuisine AS varchar(20)) FROM Cuisines;

RETURN 
(
SELECT @CuisineList
)
END

Answer 4

You need to be careful when using this method. This may not affect you now, for this query, but please keep this in mind for future applications of this method.

The problem occurs when you have a NULL value in your list. When this happens, you will get incorrect results.

For example, if your original table looks like this...

1   Blah
2   NULL
3   Foo
4   Cracker

Your function will return Foo, Cracker. The first value, Blah, will be missed by this function call. It is very easy to accommodate this, with a slight alteration to your function, like this...

CREATE FUNCTION [CombineValues] ()
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @CuisineList VARCHAR(8000);
    SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + 
        CAST(Cuisine AS varchar(20))
    FROM Cuisines
    WHERE Cuisine Is Not NULL

RETURN @CuisineList
END

By testing for NOT NULL, you will eliminate this potential problem.