[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

not a recognized built-in function name.

Posted on 2007-10-19
2
Medium Priority
?
11,654 Views
Last Modified: 2013-11-27
I have this query to create a funtion.... but it gives me an error...
Please have alook. I am using sqlserver express edition...

CREATE FUNCTION AverageQuantity(@funcType varchar(20))
RETURNS numeric AS
BEGIN
      DECLARE @average numeric
      SELECT @average = AVG(quantity) FROM Inventory WHERE type=@funcType
      RETURN @average
END
GO
Select ID, NAME, dbo.AverageQuantity('DVD') AS average from Invent WHERE quantity>AverageQuantity('DVD') AND type = 'DVD'


Error msg:

Msg 195, Level 15, State 10, Line 1
'AverageQuantity' is not a recognized built-in function name.

Jack
0
Comment
Question by:JackOfPH
  • 2
2 Comments
 
LVL 8

Expert Comment

by:MrRobot
ID: 20113496
Hi there,

SQL Server scalar user defined functions must be called using 2 part names. You will see this error if you attempt to call the functions using a 1 part name. Can you check if that is the problem ?

So if your function name is "function1" and is defined in the dbo schema, then instead of

"select function1()" you should call it as "select dbo.function1()"


see:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=725530&SiteID=1
0
 
LVL 8

Accepted Solution

by:
MrRobot earned 2000 total points
ID: 20113500
so in your situation, you should use;

WHERE quantity>dbo.AverageQuantity('DVD')

good luck =)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question