CODEWITHSUNDEEP
sqlsql-server
Alex Coplan
Alex Coplan

Reputation: 13361

Why function won't call in SQL Server

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?

Upvotes: 1

Views: 1440

Answers (3)

Cade Roux
Cade Roux

Reputation: 89661

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.

Upvotes: 3

Sparky
Sparky

Reputation: 15075

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...

Upvotes: 0

MatBailie
MatBailie

Reputation: 86716

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
);​​​​​​​​​​​

Upvotes: 0

Related Questions