Why function won't call in SQL Server

Question

I'm trying to create / call a function on the Stack Exchange Data Explorer - I haven't done much SQL Server before but only MySQL.

Why won't it let me call this function I've just made?

-- Get Post with Best Comment on Site
-- Gets the Post with the Best Comment on the Site and Associated Data

CREATE FUNCTION typeOfPost
(@PostId int(11))
RETURNS varchar(30)
AS
BEGIN
declare @PostTypeId int(3)
select @PostTypeId = (SELECT PostTypeId FROM posts WHERE PostId = @PostId)
return (SELECT Name FROM PostTypes WHERE Id = @PostTypeId)
end

SELECT PostId, typeOfPost(PostId) AS [Post Type]
FROM comments 
WHERE Score = (
SELECT max(Score) 
FROM comments
);

It gives:

"SELECT"."typeOfPost" is not a recognized built in function name.

So I looked at examples of function calls in SQL Server and I saw a lot had ".dbo" on the front. If I put that on I get this:

Incorrect syntax near the keyword 'SELECT'.

Can anyone explain what's wrong with my function?

Try using `dbo.typeOfPost(PostId)` to call your function. For some reason, the function call needs the schema name. I can't remember why at the moment. [Is it possible to call a user-defined function without the schema name?](http://stackoverflow.com/questions/502949/is-it-possible-to-call-a-user-defined-function-without-the-schema-name) — Beno, Dec 23 '11 at 03:29
@Beno - afaik it's to differentiate between system functions and user defined functions. And to be a royal pain in the butt :) — MatBailie, Dec 23 '11 at 03:33
Try changing INT(11) to INT and INT(3) to INT. SQL Server doesn't let you specify a size for an INT data type — Sparky, Dec 23 '11 at 03:48

score 3 · Accepted Answer · answered Dec 23 '11 at 05:09

I cleaned up your code so it's valid (no size on ints, correct column name for Posts.Id):

CREATE FUNCTION typeOfPost
(@PostId int)
RETURNS varchar(30)
AS
BEGIN
declare @PostTypeId int
select @PostTypeId = (SELECT PostTypeId FROM posts WHERE Id = @PostId)
return (SELECT Name FROM PostTypes WHERE Id = @PostTypeId)
end

And then you get:

Error: CREATE FUNCTION permission denied in database 'StackOverflow.Exported'.\

You can't create objects in StackOverflow Data Explorer. The best object creation you can hope for is table variables.

In addition, the reason you get the error is that when the batch is syntax checked, the function doesn't exist, so the second statement won't work. In traditional SSMS environment, you would create the function in a batch and then execute using the function in another batch. This can be done in a single file using the GO batch separator. This is a feature of SSMS and some other tools (and can be overridden in the options).

In addition, the problem you are trying to solve is not normally one solved with scalar functions (and certainly not ones which individually make trips to tables to retrieve data). Normally you would handle this very simply with a JOIN, which is a lot more accessible to the optimizer than a scalar function, which tend to be treated as block boxes.

score 0 · Answer 2 · answered Dec 23 '11 at 03:30

0

Prefix the function with the function's schema name. Probably just dbo., unless you specified something different.

SELECT PostId, dbo.typeOfPost(PostId) AS [Post Type]
FROM comments 
WHERE Score = (
SELECT max(Score) 
FROM comments
);

answered Dec 23 '11 at 03:30

MatBailie

83,401
18
103
137

@AlexCoplan - Run the function script on its own and see if it is successful. Then run your query on its own. *I think that*, as phrased at the moment, the query is being interpreted as another command in your function. This means that your function is creating the error, not your query. *[This may involve you ALTERing (or DROP and reCREATE) the function.]* – MatBailie Dec 23 '11 at 03:43
I'm off to bed now, I hope what I've written helps. If not, I hope someone else can help you :) – MatBailie Dec 23 '11 at 03:45

score 0 · Answer 3 · answered Dec 23 '11 at 04:02

You can get the same results without the function call like this:

SELECT TOP 1 c.postId,Pt.Name as [Post Type]
FROM comments c
JOIN posts p ON c.PostID=p.postID
JOIN PostTypes Pt on Pt.postTypeID=p.PostTypeId
ORDER BY c.Score DESC

I think your error message is caused by the INT(xx) syntax, which SQL doesn't accept. Also, add the GO keyword between your function definition and your SELECT clause that uses the function...

Why function won't call in SQL Server

3 Answers3