在 SQL Server 2005 中创建 UDF 时出错

发布于 2024-07-05 08:51:50 字数 558 浏览 11 评论 0原文

我尝试在 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 具有结构：

CuisineID INT PK,
Cuisine VARCHAR(20)

当我尝试创建上述函数时，出现错误：

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

我究竟做错了什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遇见了你 2024-07-12 08:51:50

使用此方法时需要小心。对于此查询，这现在可能不会影响您，但请记住这一点以便将来应用此方法。

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

例如，如果您的原始表如下所示...

1   Blah
2   NULL
3   Foo
4   Cracker

您的函数将返回 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

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

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.

回复收藏 0 原文

初雪 2024-07-12 08:51:50

这个答案来自原贴《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

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

回复收藏 0 原文

旧故 2024-07-12 08:51:50

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

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

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.

回复收藏 0 原文