Functions in SQL Server

Question

We are in the process of moving the back-end of a database from Access to SQL Server, and request help for how to implement in SQL Server a user-defined function that we previously used in Access. (Or if there’s a more direct solution to what we’re doing, say that just uses a more complex SQL statement than I’m familiar with, that would be fine too.)

So we currently have a query that looks something like the following:

SELECT StaffLine(Docket, Lead, Reviewer) AS OfficeStaff, Docket, FiledDate, Lead, Reviewer, Status, [a lot of other fields]
FROM tCases
WHERE Status = 1;

Here, OfficeStaff is computed from the StaffLine function. StaffLine is a function that just builds a full statement of all of the assigned staff members, pulling from two different tables where that data is stored. The key team members (team lead and reviewer) are stored in the main table (tCases). Then all of the names of the other team members are stored in a related table called tMembers. So a value of OfficeStaff typically looks like the following:

Office Staff:  John Doe (lead), Bob Jones, Billy Bob, Pat Jones, Jane Doe (reviewer)

We want to implement a function like our StaffLine function, but in SQL Server. I’ve pasted our StaffLine function below. I’ve researched a fair bit on how to build user-defined functions using Programmability in our SQL Server Management Studio, but I haven’t yet been able to make enough sense out of the documentation I have found. So any help on what the function would like when implemented in Management Studio, and where exactly I would put it, is very much appreciated.

Current VBA user-defined function in Access:

Public Function StaffLine(Docket As String, _
                          Lead As Variant, _
                          Reviewer As Variant _
                          ) As String
    ' Lead and Reviewer are Variants because they are sometimes Null, and String can't handle Nulls.

    ' Start off building string by adding text for Lead
    StaffLine = "Office Staff: " & Lead & " (lead), "

    ' Next add text for any non-lead team members
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MemberName FROM tMembers WHERE mDocket = '" & Docket & "'")

    ' Check to see if the recordset actually contains rows
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            StaffLine = StaffLine & rs!MemberName & ", "
            'Move to the next record.
            rs.MoveNext
        Loop
    End If

    ' Finally, add text for reviewer
    StaffLine = StaffLine & Reviewer & " (reviewer)"

End Function

Answer 1

Here is how to create the function, you will need to update the "max" size of the parameter with the actual size of your variables

    CREATE FUNCTION  [dbo].[StaffLine](@Docket varchar(max), @Lead Varchar(max) = null, @Reviewer Varchar(Max) = null)  RETURNS varChar(max)
    AS  
    BEGIN 
        Declare @StaffLine as varChar(max) = ''
        Declare @Temp TABLE (ID int identity, MemberName varchar(100))
        Declare @row_count as int = 1
        Declare @total_records as int

        --Fill hte table to loop
        Insert Into @Temp
        SELECT MemberName 
        FROM tMembers WHERE mDocket = @Docket

        Set @StaffLine = 'Office Staff: ' + ISNULL(@Lead, '') + ' (lead), '

        Set @total_records = (Select Count(*) from @Temp) -- Get total records to loop
        While @row_count <= @total_records
        Begin            
            Set @StaffLine += (Select MemberName +  ', '
            From @Temp Where ID = @row_count)

            Set @row_count += 1
        End
        SET @StaffLine +=  ISNULL(@Reviewer, '') + ' (reviewer)'
        RETURN @StaffLine
    END

Then you just use it like this:

SELECT dbo.StaffLine(Docket, Lead, Reviewer) AS OfficeStaff, Docket, FiledDate, Lead, Reviewer, Status, [a lot of other fields]
FROM tCases
WHERE Status = 1;