Solved

function is not a recognized built-in function name.

Posted on 2014-10-13
6
303 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

790 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