SQL Server中的功能

Question

我们正在将数据库的后端移动到访问SQL Server，并请求如何在SQL Server中实现一个我们以前使用的用户定义的功能在访问中。(或者，如果我们对我们正在做的事情有更直接的解决方案，那就是使用比我熟悉的更复杂的SQL语句，那也很好。(

因此，我们目前有一个查询，看起来如下：

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

在这里，从员工线功能中计算出官方。StaffLine是一个功能，仅构建了所有分配的员工的完整声明，从存储数据的两个不同表中提取。主要团队成员(团队负责人和审阅者(存储在主桌(TCASE(中。然后，将其他团队成员的所有名称存储在称为Tmembers的相关表中。因此，Officestaff的价值通常如下：

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

我们希望在SQL Server中实现一个函数。我在下面粘贴了我们的员工功能。我已经研究了如何在我们的SQL Server Management Studio中使用可编程性构建用户定义的功能，但是我还没有找到足够有意义的文档。因此，对在管理工作室中实施该功能的任何帮助以及我确切地提出的任何帮助。

都非常感谢。

当前VBA用户定义的函数访问：

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

这是如何创建函数的方法，您将需要使用变量的实际大小来更新参数的"最大"大小

    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

然后，您只是这样使用它：

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

Answer 2

这是这样做的" sql方式"。

首先创建此视图：

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
)

在SQL中，此视图的作用就像函数或地图 - 它定义了如何为所有案卷编号制作员工字符串。

在SQL中，我们喜欢在集合中工作 - 在SQL中，我们永远不应该用循环迭代，循环是邪恶的！

然后进行选择，只需加入：

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！简单。

Answer 3

这是一个使用XML的完整工作示例，并应用：

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

这是结果集：

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