47

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Abbas
  • 4,948
  • 31
  • 95
  • 161

3 Answers3

92

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
Akhil
  • 7,570
  • 1
  • 24
  • 23
  • How to get the result of this query in a variable in php? I mean $var = mssql_query(function which return int value).I am not getting the result inside $var.Its showing like 'Resourceid #76' @Akhil – Techy Apr 14 '15 at 11:57
5

Try

SELECT dbo.function (parameters)
Vijay Singh Rana
  • 1,060
  • 14
  • 32
  • This does not work, at least for me. Remove the '* from' and it does work. – Al Lelopath Oct 17 '14 at 20:05
  • How to get the result of this query in a variable in php? I mean $var = mssql_query(function which return int value).I am not getting the result inside $var.Its showing like 'Resourceid #76' @Abbas – Techy Apr 14 '15 at 11:59
1

Or you can simply use PRINT command instead of SELECT command. Try this,

PRINT dbo.fn_HomePageSlider(9, 3025)
Adnan Sharif
  • 919
  • 7
  • 17