Link to home
Start Free TrialLog in
Avatar of real9555
real9555

asked on

Function

How can I insert a function into a query? I have a select query like the below and would like tio insert the function nameed below in the query:

I have a simple select query that I would like to insert a function in the middle . Name of the function "fnryNbr". I would like it in the place of the second line from below:

SELECT  Staff.FirstName + ' ' + Staff.LastName AS Staff_Name,

ryNb.Nbr,

 [Case].EndDate, CaseStatus.Name AS [case Status],
               Appointment.DateTime, Appointment.Id AS ApptID, Appointment.Status AS ...

would I need to link the function to the tables. I have never added a function to a query. Please help :)
SOLUTION
Avatar of Luis Pérez
Luis Pérez
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of real9555
real9555

ASKER

it gave me the following error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.fnryNbr()" could not be bound.

The reason for this function is that I want to select the top number for each staff

I have two tables: staff and MR#. one staff may have more than one number so I want to select the highest digit numbers. I am not sure if there is an easier way to do that. In this function is the following codes

SELECT TOP 1 @result = fnryNbr FROM BranchNbr
      WHERE StaffID = @StaffID
      ORDER BY BranchId
If all you want to do is select the highest number you probably don't need the function.
If you can show us your table structures we might be able to help you with your query without the function.

If you want to use the function you can do something like:

SELECT  Staff.FirstName + ' ' + Staff.LastName AS Staff_Name,

dbo.fnryNbr(Staff.StaffId) AS BranchNbr,

 [Case].EndDate, CaseStatus.Name AS [case Status],
               Appointment.DateTime, Appointment.Id AS ApptID, Appointment.Status AS ...
Structure

staffId      uniqueidentifier      Unchecked
yNbr      varchar(50)      Unchecked
FacilityId       int                          Unchecked



SELECT  Staff.LastName, yNbr.BranchNbr
 FROM     BranchNbr INNER JOIN
               Staff ON BranchNbr.StaffID = staff.StaffID
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial