CODEWITHSUNDEEP
sql-server-2012
Daniel Stallard
Daniel Stallard

Reputation: 79

Why does i say my function doesnt exist even thought the function was created?

I have to create a Function to Calculate invoiceID,InvoiceTotal - PaymentTotal - CreditTotal. So I created a function named fnNetOwed.I have to run it in this syntax:

Select invoiceId, dbo.fnNetOwed(invoiceid) from invoices
Where dbo.fnNetOwed (invoiceid) >0

And get the same results as if I did

 Select invoiceID,InvoiceTotal - PaymentTotal - CreditTotal From     Invoices 
Where InvoiceTotal - PaymentTotal - CreditTotal>0

This is my code for creating it: CREATE FUNCTION fnNetOwed (@GetInvoiceId Money) RETURNS Int

BEGIN
RETURN     (SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS 'OWED'
        FROM Invoices
        WHERE @GetInvoiceID = Invoices.InvoiceID);
END 
GO

This is what i am using to call it/execute: Select InvoiceId, fnNetOwed(98) from Invoices Where fnNetOwed(98) >0; This is the ERROR I get when i run it:

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

Upvotes: 1

Views: 33

Answers (2)

Raymer Ortiz
Raymer Ortiz

Reputation: 11

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

Upvotes: 1

Khaled
Khaled

Reputation: 421

Try to call it using your schema name : SELECT schema.fnNetOwed(98)

Upvotes: 1

Related Questions