Solved

function is not a recognized built-in function name.

Posted on 2014-10-13
6
334 Views
Last Modified: 2014-10-13
I am getting:

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

(using SQLEXPRESS12 apparently iif() is acceptable to use)

After creating
Create FUNCTION dbo.fnHasOrders
(
 -- the parameters for the function here
 @CustomerID   int
)
RETURNS  varchar(20)
AS
BEGIN

declare @response  varchar (20)

Select @response = iif(isnull(o.OrderID,0)=0,'','<-- has orders') 
From tblCustomers c left join tblOrders o 
on o.CustomerID = c.CustomerID Where c.CustomerID=@CustomerID

 -- Return the result of the function
 RETURN @response

End

Open in new window


FYI: This function shows up under Functions/Scalar-value function folder at SSMS



Select CustomerID, fnHasOrders(CustomerID) from tblCustomers
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40379133
add
        dbo.

select CustomerID, dbo.fnHasOrders(CustomerID) from tblCustomers
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40379146
My favorite expert!

Thanks for the hint. There is a followup question.

Jack Smith            <-- has orders
Robert Nissan           <-- has orders

I want to line up <-- has orders portions in:

       SQL.ExecQuery("SELECT CustomerID, FirstName + ' ' + LastName + '     ' +" &
                     " dbo.fnHasOrders (CustomerID) as CustName From tblCustomers")


by having variable number of blank spaces instead of just '     ' above.

brb
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40379151
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40379211
:) you are too kind, but thank you!

Cheers, Paul
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40379213
btw: you can also use the SPACE() function

 SQL.ExecQuery("SELECT CustomerID, substring(FirstName + ' ' + LastName + space(50) ,1,50) +" &
                     " dbo.fnHasOrders (CustomerID) as CustName From tblCustomers")
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40379226
Appreciate the tip.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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