Posted on 2012-09-18
Last Modified: 2012-10-02
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,


 [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 :)
Question by:real9555
    LVL 25

    Assisted Solution

    by:Luis Pérez
    SELECT  Staff.FirstName + ' ' + Staff.LastName AS Staff_Name,
    dbo.fnryNbr() AS TheNameThatYouWant,

    Hope that helps.

    Author Comment

    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
    LVL 13

    Expert Comment

    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 ...

    Author Comment


    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
    LVL 13

    Accepted Solution

    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, ...

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now