SQL Server 存储过程:根据一对多关系筛选表

Question

我正在尝试创建一个存储过程，该过程将对具有多个关系的表执行查询。

示例主表：

dbo.Provider
+----+-----------+------------+
| Id | FirstName |  LastName  |
+----+-----------+------------+
|  1 | Lor       | Blair      |
|  2 | Wanda     | Montgomery |
|  3 | Inez      | Palmer     |
|  4 | Cecilia   | Adkins     |
|  5 | Jessica   | Swanson    |
+----+-----------+------------+

一对多关系船示例

dbo.ProviderSpecialty
+----+----------------+-----------+
| Id |   Specialty    |   Type    |
+----+----------------+-----------+
|  1 | ANESTHESIOLOGY | Primary   |
|  1 | CARDIOLOGY     | Secondary |
|  2 | ANESTHESIOLOGY | Primary   |
|  3 | ANESTHESIOLOGY | Primary   |
|  3 | AMBULANCE      | Secondary |
|  4 | AMBULANCE      | Primary   |
|  5 | CARDIOLOGY     | Primary   |
|  5 | AMBULANCE      | Secondary |
+----+----------------+-----------+
dbo.ProviderAddress
+----+-------+-----------+
| Id |  Zip  |   Type    |
+----+-------+-----------+
|  1 | 11510 | Primary   |
|  2 | 11510 | Primary   |
|  2 | 13090 | Secondary |
|  2 | 96814 | Previous  |
|  3 | 13090 | Primary   |
|  4 | 13090 | Primary   |
|  5 | 96814 | Primary   |
|  5 | 11510 | Secondary |
+----+-------+-----------+

我有dbo.ProviderHealthPlan和dbo.ProviderHospital，但他们会遵循相同的方法。

我的存储过程接受FirstName、LastName、Specialty和Zip作为参数。

如果参数为 null ，则不要对该参数进行筛选。如果参数具有值，则仅显示参数匹配的提供程序。

另外，我想在结果中显示所有特色菜和拉链。

例如，搜索Specialty = 'AMBULANCE'(所有其他空值(将产生：

+----+-----------+----------+---------------------------+--------------+
| Id | FirstName | LastName | Specialties               | Zips         |
+----+-----------+----------+---------------------------+--------------+
| 3  | Inez      | Palmer   | ANESTHESIOLOGY, AMBULANCE | 13090        |
| 4  | Cecilia   | Adkins   | AMBULANCE                 | 13090        |
| 5  | Jessica   | Swanson  | CARDIOLOGY, AMBULANCE     | 96814, 11510 |
+----+-----------+----------+---------------------------+--------------+

Specialty = 'AMBULANCE'和Zip = '13090'上的申报者将返回：

+----+-----------+----------+---------------------------+--------------+
| Id | FirstName | LastName | Specialties               | Zips         |
+----+-----------+----------+---------------------------+--------------+
| 3  | Inez      | Palmer   | ANESTHESIOLOGY, AMBULANCE | 13090        |
| 4  | Cecilia   | Adkins   | AMBULANCE                 | 13090        |
+----+-----------+----------+---------------------------+--------------+

显然，对名字或姓氏的过滤器是不言自明的。我有 4 种不同的一对多关系要过滤，这只是一些示例数据。

任何关于从哪里开始的帮助都会很棒。

谢谢！

Answer 1

我建议阅读以下内容：

https://sqlwithmanoj.com/2011/12/30/creating-stored-procedures-with-dynamic-search-filter/

Answer 2

如果要

筛选是否传递了值，而不是筛选是否传递了 null，然后你可以像这样构建where子句：

where [someColumn] = ISNULL(@someVariable, [someColumn])
      and [someOtherCol] = ISNULL(@someOtherVar, [someOtherCol])

至于将所有关系组合到单个值中，您可以使用以 XML 形式重新生成结果的子查询，然后将其解析为字符串值。它不会很漂亮，但它绝对有效。

Answer 3

这里这样的东西应该可以工作，我没有测试它，但我认为你很容易理解发生了什么

CREATE PROC Show
@Firstname VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Specialty VARCHAR(50) = NULL
,@zip INT = NULL
AS 
IF (@Firstname IS NOT NULL 
    AND @LastName IS NOT NULL
    AND @Specialty IS NOT NULL 
    AND @zip IS NOT NULL )
    Begin
            SELECT * FROM MainTable M
            JOIN Specialty s ON s.ID = m.id
            JOIN zip z ON z.ID = m.ID
            WHERE m.FIrstname = @Firstname AND m.lastname = @LastName AND s.specialty = @Specialty AND z.zip = @zip

    END 
        ELSE
           BEGIN
               IF( @Firstname IS NOT NULL OR @LastName IS NOT null)
                  Begin
                     SELECT * FROM MainTable M
                     JOIN Specialty s ON s.ID = m.id
                     JOIN zip z ON z.ID = m.ID
                     WHERE (@Firstname IS NULL OR m.Firstname = @Firstname AND @LastName IS NULL OR m.Lastname = @LastName)
                  END 
                     ELSE 
                             BEGIN
                               SELECT * FROM MainTable M
                               JOIN Specialty s ON s.ID = m.id
                               JOIN zip z ON z.ID = m.ID
                               WHERE( @Specialty IS NULL or s.specialty = @Specialty AND @zip IS NULL OR zip = @zip )
                             END
           END