金融業經常僅允許透過預存程序進行資料庫存取，往往一個資料表因為不同的查詢條件而產生多個預存程序檔，以下將提供一個只需產生一個預存程序即可查詢多條件的方法
以下使用Test 資料表為例
預存程序通用查詢方法範例
重點提醒
1. 傳入參數預設為Null
2. Were 查詢條件為下
([No] = @No or @No is null) AND
([Name] = @Name or @Name is null) AND
([Value] = @Value or @Value is null) AND
([CreatedTime] = @CreatedTime or @CreatedTime is null)
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SP_GetTest] Script Date: 2021/11/16 下午 05:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_GetTest]
-- Add the parameters for the stored procedure here
@No int = NULL,
@Name varchar(10) = NULL,
@Value varchar(10) = NULL,
@CreatedTime datetime2(7) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM [dbo].[Test]
WITH (ROWLOCK, UPDLOCK)
WHERE
([No] = @No or @No is null) AND
([Name] = @Name or @Name is null) AND
([Value] = @Value or @Value is null) AND
([CreatedTime] = @CreatedTime or @CreatedTime is null)
RETURN 0
END
透過SQL Script 測試
重點提醒
1. 所有參數都需要傳入，包含Null值的參數
程式碼:
DECLARE
@No int = 1,
@Name varchar(10) = NULL,
@Value varchar(10) = '123',
@CreatedTime datetime2(7) = NULL
execute [dbo].[SP_GetTest] @Name, @Name, @Value, @CreatedTime結果:
透過C# 程式測試
重點提醒
1. 只需傳入需查詢的參數條件，非Null值的參數
程式碼:
var repo = new Repository<Test>(CLC.Common.StoreProcedure.SP_GetTest);
repo.AddParList(new System.Data.SqlClient.SqlParameter("@Value", "456"));
var list = repo.Query();
res.ResParams = JObject.FromObject(list);結果:
