• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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 :)
0
real9555
Asked:
real9555
  • 2
  • 2
2 Solutions
 
Luis PérezSoftware Architect in .NetCommented:
SELECT  Staff.FirstName + ' ' + Staff.LastName AS Staff_Name,
dbo.fnryNbr() AS TheNameThatYouWant,
...

Hope that helps.
0
 
real9555Author Commented:
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
0
 
LIONKINGCommented:
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 ...
0
 
real9555Author Commented:
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
0
 
LIONKINGCommented:
What you would do is group by all the fields except the BranchNumber, which would be a MAX.

Something like:

SELECT  Staff.LastName, Staff.col1, Staff.col2, ... , MAX(yNbr.BranchNbr) BranchNbr
 FROM     BranchNbr INNER JOIN
               Staff ON BranchNbr.StaffID = staff.StaffID
GROUP BY Staff.LastName, Staff.col1, Staff.col2, ...
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now