how to create and call scalar function in sql server 2008

Question

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.

Akhil · Accepted Answer · 2011-12-23 18:59:29Z

91

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

answered Dec 23, 2011 at 18:59

Akhil

7,6201 gold badge26 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Techy Over a year ago

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

Vijay Singh Rana · Accepted Answer · 2015-02-10 10:51:44Z

5

Try

SELECT dbo.function (parameters)

edited Feb 10, 2015 at 10:51

answered May 23, 2014 at 5:18

Vijay Singh Rana

1,10014 silver badges32 bronze badges

2 Comments

Al Lelopath Over a year ago

This does not work, at least for me. Remove the '* from' and it does work.

Techy Over a year ago

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

Adnan Sharif · Accepted Answer · 2019-02-14 13:28:52Z

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

PRINT dbo.fn_HomePageSlider(9, 3025)

Collectives™ on Stack Overflow

how to create and call scalar function in sql server 2008

3 Answers 3

1 Comment

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Linked

Related