不使用動態sql重寫select陳述句-有解無憂

我有一個包含以下陳述句的程式：

Create procedure usp_personCountry
@inPersonId Int,
@inType varchar(100)
As
Begin

If @inType = 'Admin'
Begin
  Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
 Where p.personId = @inPersonId
  and pa.type     = 'Primary'
End
Else If @inType = 'Manager'
Begin 
 Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  Inner Join dbo.personCountryManager pcm
    on pa.personId = pcm.personId
 Where p.personId = @inPersonId
  and pa.type     = 'Manager'
End
Else If @inType = 'User'
Begin 
 Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  Inner Join dbo.personCountryUser pcm
    on pa.personId = pcm.personId
 Where p.personId = @inPersonId
  and pa.type     = 'User'
End
Else
Begin 
 Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  Inner Join dbo.personCountryTeam pcm
    on pa.personId = pcm.personId
 Where p.personId = @inPersonId
  and pa.type     = 'Team'
End

End /* End of procedure */

在上述程序中，select 陳述句對于所有條件都是相同的。每次我必須添加一個新列時，我都必須將它添加到所有 4 個陳述句中，并且很有可能我最終會錯過在某個時候向其中一個陳述句添加一列。有沒有辦法重寫這個 sql 查詢，以便 select 陳述句只使用一次，并且根據傳入的型別我們可以構造查詢？我想在不使用動態 sql 的情況下完成此操作。我想知道是否有任何方法可以做到這一點。謝謝！

uj5u.com熱心網友回復：

您可以將 if 條件行內，例如使用CASE運算式。但是我確實質疑distinct. 無論如何，您可以像這樣減少重復次數來做到這一點，但這可能會非常浪費：

  Select /* Distinct -- why? */ p.personid, 
    p.name, p.email, pc.country, pa.attributeId
  INTO #blat From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
 Where p.personId = @inPersonId
  and pa.type     = CASE @inType
                    WHEN 'Admin'   THEN 'Primary'
                    WHEN 'Manager' THEN 'Manager'
                    WHEN 'User'    THEN 'User'
                    ELSE                'Team' END;

IF @inType = 'Admin'
BEGIN
  SELECT * FROM #blat;
END

IF @inType = 'Manager' 
BEGIN
  SELECT * FROM #blat AS b WHERE EXISTS 
  (
    SELECT 1 FROM dbo.personCountryManager WHERE personId = b.personId
  );
END 

IF @inType = 'User' 
BEGIN
  SELECT * FROM #blat AS b WHERE EXISTS 
  (
    SELECT 1 FROM dbo.personCountryUser WHERE personId = b.personId
  );
END 

IF @inType = 'Team' 
BEGIN
  SELECT * FROM #blat AS b WHERE EXISTS 
  (
    SELECT 1 FROM dbo.personCountryTeam WHERE personId = b.personId 
  );
END

此外，我懷疑隨著這些表變得越來越大，如果有任何資料傾斜，動態 SQL 實際上是一個更好的選擇。

DECLARE @sql nvarchar(max) = N'SELECT 
    p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId';

SET @inType = CASE @inType WHEN 'Admin' THEN 'Primary' ELSE @inType END;

IF @inType <> 'Primary'
BEGIN
  SET @sql  = N'
    Inner Join dbo.'   QUOTENAME(N'personCountry'   @inType)   ' pcm
      on pa.personId = pcm.personId';
END

SET @sql  = N'
  Where p.personId = @inPersonId
  and pa.type     = @inType';

EXEC sys.sp_executesql @sql, 
  N'@inPersonId int, @inType varchar(100)',
    @inPersonId,
    @inType;

uj5u.com熱心網友回復：

可以這樣做，但結果將是一個非常非常復雜的查詢，并且性能很差。如果（何時）表變大，可能會嚴重消耗服務器資源。

動態 SQL 是一種選擇，但在這里我認為它的使用會很聰明，而不是聰明（因為“聰明”通常是“聰明”的敵人）。這些查詢非常不同，我建議為每種情況（@inType 值）創建一個單獨的存盤程序，或者直接從應用程式呼叫它們，或者使用這個程序作為包裝器來呼叫適當的存盤程序。

基礎表設計可能會有所改進？不能用我們掌握的資訊來判斷。

下面是我想出的代碼——它不是一個完整的解決方案。我開始著手處理額外連接的部分，這個邏輯是它變得過于混亂而無法成為合理查詢的地方。

--  Reset, to simplify logic
If @inType = 'Admin'
    SET @inType = 'Primary'


  Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  --  from 0 or 1 INNER JOIN to 3 left outer joins (and change alias)
  Left Outer Join dbo.personCountryManager pcm
    on pa.personId = pcm.personId
  Left Outer Join dbo.personCountryUser pcu
    on pa.personId = pcu.personId
  Left Outer Join dbo.personCountryTeam pct
    on pa.personId = pct.personId
 Where p.personId = @inPersonId
  --  Now, presumes parameter matches pa-type
  --  This is a problem if that final "else" is not always "Team"
  and pa.type = @inType

上面對引數@inType 做了一些假設，并且仍然需要檢查“如果@inType 是X 并且一個相關的左外連接表找到了一個條目”，那就是它變得非常丑陋的時候。

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/405453.html

標籤：

上一篇：如何在T-SQL中專門使用LIKE找到特定的符號字符組合

下一篇：T-SQL檢索上周六的最近6周資料