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 the the "SQL Way" of doing it.

First create this view:

CREATE VIEW Staff_String AS
(
  SELECT Docket, 'Office Staff : ' || COALESCE(C.Lead || ' (lead),','') || 
                  STRING_AGG(M.MemberName,', ') ||
                  C.Reviewer || '(reviewer)' as OfficeStaff
  FROM tCases C
  JOIN tMembers M ON M.mDocket = C.Docket
  GROUP BY C.Docket, C.Lead, C.Reviewer
)

In SQL this view acts like a function or map -- it defines how to make a staff string for all docket numbers.

In SQL we like to work on sets -- in SQL we should never iterate with a loop, loops are evil!!

Then to do your select you just join to it:

SELECT Staff_String.OfficeStaff, Docket, FiledDate, Lead, Reviewer, Status, [a lot of other fields]
FROM tCases 
LEFT JOIN Staff_string ON tCases.Docket = Staff_string.Docket
WHERE tCases.Status = 1;

Tad da! Easy.

Answer 2

Here is a full working example using xml and cross apply:

declare @tCases table (
    tCasesID int identity(1,1) not null primary key clustered
,   docketID int not null
,   staffID  int not null
,   GivenName nvarchar(255) not null
,   SurName nvarchar(255) not null
,   Role_   nvarchar(255) not null
)

insert into @tCases (docketID, staffID, GivenName, SurName, Role_)

select 1, 100, 'bob', 'richards', 'reviewer' union all
select 1, 110, 'john', 'doe', 'lead' union all
select 1, 112, 'jane', 'doe', 'reviewer';

declare @tMember table (
    tMemberID int identity(1,1) not null primary key clustered
,   docketID int not null
,   staffID     int not null
,   GivenName   nvarchar(255) not null
,   SurName     nvarchar(255) not null
);

insert into @tMember (docketID, staffID, GivenName, SurName)

select 1, 133, 'Mary', 'Jones' union all
select 1, 134, 'Tom', 'Jones' union all
select 1, 105, 'Jimmy', 'Jon' union all
select 1, 109, 'Marsha', 'Marsha';


;with cte as (
    select docketid
         , GivenName
         , SurName
      from @tCases

    union all

     select docketid
         , GivenName
         , SurName
       from @tMember
       )

    select distinct a.docketID
         , x.list
      from @tCases a
    cross apply (

                select stuff 
                            ( 
                            (select  ', ' +GivenName + ' ' + SurName 
                  from cte b
                 where a.docketid = b.docketid
                 for xml path ('')
                 )  , 1, 1, '') as list ) as x

Here is the result set:

docketID    list
1           bob richards, john doe, jane doe, Mary Jones, Tom Jones, Jimmy Jon, Marsha Marsha

Answer 3

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;