實作動態參數的Stored Procedure查詢

我們在撰寫查詢功能的Stored Procedure，會傳入若干的參數，而一般的需求時，當使用者有輸入條件，該參數才當作是篩選的條件，底下例子是摘錄網友的問題，他這邊有兩個資料表

--Table: Member
CREATE TABLE [dbo].[Member](
 [MemberId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NULL,
 [CreateDate] [datetime] NULL,
 CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED 
(
 [MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--Table LoginRecord

CREATE TABLE [dbo].[LoginRecord](
 [RecordId] [int] IDENTITY(1,1) NOT NULL,
 [MemberId] [int] NULL,
 [CreateDate] [datetime] NULL,
 CONSTRAINT [PK_LoginRecord] PRIMARY KEY CLUSTERED 
(
 [RecordId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
 ON [PRIMARY]
) ON [PRIMARY]
GO

這裡我們要查詢登錄的記錄，輸入的參數有

• MemberID: 帳號



• Name: 姓名



• StartLoginDate/EndLoginDate:登錄起訖



• StartCreateDate/EndCreateDate:資料建立起訖

有這四組的條件，至少會輸入一的條件，條件是以AND來組合，如果沒輸入的條件，就不會加入AND條件篩選，這如果以.net程式是非常好解決的，以下例子為了防止SQL INJECTION使用Parameter

        // 假設已經資料庫連線

        System.Text.StringBuilder sbSQL = new StringBuilder(500);

        List<SqlParameter> cParameters = new List<SqlParameter>();

        // 增加一個 1 = 1 的預設條件以便可以不需先判斷是否已經有條件, 
        //要不要前面加AND, 而可以順序任意加入條件 

        sbSQL.Append(select * from Member m inner join LoginRecord l on m.MemberId = 
        l.MemberId WHERE 1 = 1 ");

        if (!String.IsNullOrEmpty(txtCondition1.Text)) {
            sbSQL.Append("  m.name like @Column1");
            cParameters.Add(new SqlParameter("@Column1", txtCondition1.Text));
        }
        if (!String.IsNullOrEmpty(txtCondition1.Text || 
                !String.IsNullOrEmpty(txtCondition1.Text )  )
        {
            sbSQL.Append(" l.CreateDate between @Column2 and @Column3");
            cParameters.Add(new SqlParameter("@Column2", txtCondition2.Text));
            cParameters.Add(new SqlParameter("@Column2", txtCondition3.Text));
        }

        SqlCommand oCommand = new SqlCommand(sbSQL.ToString, conn);
        if (cParameters.Count != 0) 
        {
            oCommand.Parameters.AddRange(cParameters.ToArray());
        } 

    

但是如果要把參數都傳入Stored Procedure，要怎麼做呢?


這邊可以借助IsNull參數，來傳斷傳入的參數是否為Null,如果是Null就令其=要篩選的變數，跟1 = 1一樣，這個條件是ByPass的意思, 以MemberID為例，如果@MemberID 是null，條件就會變成Member = Member，一定會成立，就等於沒作用的條件，以下是完整的 Code

Alter procedure  SP_GetMember
 @MemberId int,
 @Name nvarchar(50),
 @StartLoginDate datetime,
 @EndLoginDate datetime,
 @StartCreateDate datetime,
 @EndCreateDate datetime
 as

 select * from Member m inner join LoginRecord l on m.MemberId = l.MemberId
 where 
 ( m.name like '%' + isnull(@Name,'') + '%') and
 m.MemberId = Isnull(@MemberId, m.MemberId) and
 l.CreateDate >= IsNull(@StartLoginDate, l.CreateDate) and 
                l.CreateDate <= Isnull(@EndLoginDate,l.CreateDate) and
 m.CreateDate  >= IsNull(@StartCreateDate, m.CreateDate) and 
                m.CreateDate <=  Isnull(@EndCreateDate,m.CreateDate) 

http://www.codeproject.com/Articles/21234/Implementing-Dynamic-WHERE-Clause-in-Static-SQL