如何在sql server 2008中创建和调用标量函数 [英] how to create and call scalar function in sql server 2008

问题描述

我创建了一个标量函数，它创建成功，但是当我使用select语句调用函数时，它说无效对象，我修改了函数，我得到了成功完成的消息命令，但是当我调用函数时，我得到同样的错误。下面是我试图调用的函数：

pre $ ALTER FUNCTION [dbo]。[fn_HomePageSlider]
（
@PortalID int，
@ArticleID int
）
RETURNS NVARCHAR（MAX）
AS
BEGIN
DECLARE @HTML NVARCHAR（MAX）
SET @HTML ='';
声明@Title varchar（1000）
选择@ Title = Title from CrossArticle_Article c其中c.Id=@ArticleID
选择@HTML = @HTML +'< div class =homeSlider >
< div class =text>'+ ISNULL（c.Title，''）+'< / div>
< / div>'
FROM CrossArticle_Article c INNER JOIN crossarticle_url U ON U.articleid = c.Id
INNER JOIN FREETEXTTABLE（CrossArticle_Article，TITLE，@ TITLE）as INDEX_TBL
ON INDEX_TBL。[KEY] = c.Id
WHERE INDEX_TBL.RANK> = 75 AND
c.Id<> @ArticleID AND
c.PortalId=@PortalID
GROUP BY c.Title，U.url，INDEX_TBL.RANK
ORDER BY INDEX_TBL.RANK DESC

RETURN @HTML;
END


下面是我调用函数的方式：

  SELECT * FROM dbo.fn_HomePageSlider（9，3025）
  

任何人都可以告诉我上述函数有什么问题，因为我已经成功完成了消息命令。

解决方案

如果您的调用是表值函数，则可以使用它。由于它是一个标量函数，因此您需要像这样调用它：

  SELECT dbo.fn_HomePageSlider（9，3025）AS MyResult 
  

I have created a Scalar Functions, it was created successfully, but when I call the function using select statement, it says invalid object, I altered the function, I got the message command completed successfully, but when I call the function, I gets same error. below is the function I am trying to call:

ALTER FUNCTION [dbo].[fn_HomePageSlider]
(
    @PortalID int,
    @ArticleID int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @HTML NVARCHAR(MAX)
    SET @HTML = '';
    Declare @Title varchar(1000)
    Select @Title= Title from CrossArticle_Article c where c.Id=@ArticleID
    Select @HTML = @HTML + '<div class="homeSlider">
                                <div class="text">'+ISNULL(c.Title,'')+'</div>
                            </div>'
    FROM CrossArticle_Article c INNER JOIN crossarticle_url U ON U.articleid=c.Id
    INNER JOIN FREETEXTTABLE(CrossArticle_Article,TITLE,@TITLE) as INDEX_TBL 
    ON INDEX_TBL.[KEY]=c.Id
    WHERE INDEX_TBL.RANK >= 75 AND 
    c.Id<>@ArticleID AND
    c.PortalId=@PortalID
    GROUP BY c.Title,U.url,INDEX_TBL.RANK
    ORDER BY INDEX_TBL.RANK DESC

    RETURN @HTML;
END

And below is the way I am calling the function:

SELECT * FROM dbo.fn_HomePageSlider(9, 3025)

Can anyone tell me what's wrong with the above function, as I get the message command completed successfully.

解决方案

Your Call works if it were a Table Valued Function. Since its a scalar function, you need to call it like:

SELECT dbo.fn_HomePageSlider(9, 3025) AS MyResult

这篇关于如何在sql server 2008中创建和调用标量函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！