在SQL Server 2005中创建UDF时出错

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

我正在尝试在SQL Server 2005 Express中创建一个UDF,如下所示:

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 '='.

消息102,级别15,状态1,过程CombineValues,第10行'='附近的语法不正确。

What am I doing wrong?

我究竟做错了什么?

4 个解决方案

#1

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

这个答案来自原始海报Wild Thing。请不要投票或投票。

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

#2

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.

列表中有NULL值时会出现此问题。发生这种情况时,您将得到不正确的结果。

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...

你的功能将返回Foo,Cracker。此函数调用将错过第一个值Blah。很容易适应这种情况,稍微改变你的功能,就像这样......

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.

通过测试NOT NULL,您将消除这个潜在的问题。

#3

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

尝试将SELECT更改为SET,然后通过SELECT(ing)@CuisineList结束您的功能

#4

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

Hojou,你的建议没有用,但有类似的做法:

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.

我想将此标记为答案,但由于我是提出这个问题的人,我不确定这是否合适?有什么建议?请感受评论。

#1