我已經 5 年多沒有接觸過 SQL Server 和 SQL 查詢了，現在我需要撰寫一個新的存盤程序。
許多這些存盤程序引數是可選的，因此每次執行都有可能不同的執行計劃和路徑。我想知道如果我沒有正確執行此操作，它會非常緩慢并且可能是反模式，所以我想在這里小心。
以下是我想要完成的偽代碼。
ALTER PROCEDURE GetPatients
@Active BIT,
@PatientGender NVARCHAR(1),
@PatientName NVARCHAR(50),
@PatientDOB DATE
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM PatientTable
WHERE Active = @Active
--@PatientGender could be M, F or B for both.
--If B then disregard the values in the PatientGender Column
AND (CASE @PatientGender
WHEN 'M' THEN PatientGender = 'M'
WHEN 'F' THEN PatientGender = 'F'
ELSE
END)
--@PatientName is a wildcard search
--@PatientName is null then disregard the values in the PatientName column
AND (CASE @PatientName
WHEN '' THEN --don't include this and branch
ELSE PatientName LIKE '%@PatientName%'
END)
--PatientDOB same as above. Not included here for brevity but if empty skip if
present add an AND branch to WHERE clause.
END
GO
所以我想出的是這兩種解決方案，我覺得它們都不是很好。
首先是一個丑陋的分支存盤程序......但它有效。
ALTER PROCEDURE GetPatients
@Active BIT,
@PatientGender NVARCHAR(1),
@PatientName NVARCHAR(50),
@PatientDOB DATE
AS
BEGIN
SET NOCOUNT ON;
IF @PatientGender = 'M'
BEGIN
SELECT *
FROM PatientTable
WHERE Active = @Active
AND PatientGender = 'M'
END
IF @PatientGender = 'F'
BEGIN
SELECT *
FROM PatientTable
WHERE Active = @Active
AND PatientGender = 'F'
END
ELSE
BEGIN
SELECT *
FROM PatientTable
WHERE Active = @Active
END
--and so it keeps going till every condition is covered
--it's ugly
END
其次是動態 SQL，雖然這“看起來”更好，但如果我沒記錯的話，除非沒有其他選擇，否則您應該避免使用動態 SQL。
ALTER PROCEDURE GetPatients
@Active BIT,
@PatientGender NVARCHAR(1),
@PatientName NVARCHAR(50),
@PatientDOB DATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(1000)
DECLARE @PatientGenderFilter NVARCHAR(500)
DECLARE @PatientNameFilter NVARCHAR(500)
DECLARE @PatientDOBFilter NVARCHAR(500)
SET @PatientGenderFilter = CASE
WHEN @PatientGender = 'M' THEN N'AND PatientGender = ''M'' '
WHEN @PatientGender = 'F' THEN N'AND PatientGender = ''F'' '
END
SET @PatientNameFilter = CASE @PatientName
WHEN '' THEN N''
ELSE N'AND PatientName LIKE ''%' @PatientName '%'' '
END
SET @SQL = N'SELECT * FROM PatientTable WHERE Active = ' CAST(@Active AS CHAR(1)) ' ' @PatientGenderFilter @PatientNameFilter
--PRINT @SQL
EXEC(@SQL)
END
GO
所以我的問題是：我有兩個作業查詢，但似乎都不合標準。我正在尋求確認這是最好的，或者查詢改進可能是可能的。
uj5u.com熱心網友回復：
根據您的問題，如果您的查詢引數是存盤程序中的可選引數，您可以嘗試使用OR并AND判斷您的條件。
其他事情
- 確保您已從過濾器列中為查詢添加索引。
- 您可能會遇到查詢中的引數嗅探，這可能會讓您的查詢變慢。所以你可以嘗試添加
OPTION(RECOMPILE)
查詢如下。
SELECT *
FROM PatientTable
WHERE Active = @Active
AND (@PatientGender IS NULL OR PatientGender = @PatientGender)
AND (@PatientName = '' OR PatientName LIKE CONCAT('%',@PatientName,'%'))
OPTION(RECOMPILE)
筆記
usingOPTION(RECOMPILE)將告訴 QO 不要嘗試保留執行計劃，如果您的查詢經常被查詢，這可能會讓您的 CPU 變得繁忙。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/460694.html
標籤：tsql