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 :)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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)
[Case].EndDate, CaseStatus.Name AS [case Status],
Appointment.DateTime, Appointment.Id AS ApptID, Appointment.Status AS ...
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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