not a recognized built-in function name.

Posted on 2007-10-19
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
      DECLARE @average numeric
      SELECT @average = AVG(quantity) FROM Inventory WHERE type=@funcType
      RETURN @average
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.

Question by:JackOfPH
    LVL 8

    Expert Comment

    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()"

    LVL 8

    Accepted Solution

    so in your situation, you should use;

    WHERE quantity>dbo.AverageQuantity('DVD')

    good luck =)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    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.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now