MS SQL

SQL用ISNULL選擇條件簡化程式碼

廖尉村 Johnson Liao 2020/12/21 18:38:01
70

 

1. 前言

資料庫Stored Procedure常見重複的語法,維護人員必須每段都詳細閱讀,光看這些語法需花費時間較長,可能還要花時間去做整理,當需進行調整或修改時,花費的時間及精力也需要更多,否則在某一段漏掉就會造成部份情境沒有調整好,所以盡可能的把語法簡單化,讓維護人員更好維運的工作及作業,這也是開發人員要努力的目標。

 

2. 開始前準備

實作是建立於以下版本的環境:

l  SQL Server 2017

 

3.人員登入記錄查詢 Stored Procedure實作

在這介紹人員登入記錄查詢,系統在設計登入/登出都會有軌跡,當系統異常或有需要時可以調出記錄,公司稽查人員也會定期查核這些記錄,調閱有日期區間或特定帳號,以下介紹常見的幾種寫法。

 

一、     建置資料表

SQL Script

CREATE TABLE USER_LOGIN_LOG (

    [LOGIN_LOG_ID] [bigint] IDENTITY(1,1) NOT NULL,

    [LOGIN_TIME] [datetime] NULL,

    [USER_ACCOUNT] [varchar](100) NOT NULL,

    [CLIENT_IP] [varchar](23) NOT NULL,

    [DEVICE_KIND] [varchar](20) NOT NULL,

    [CNAME] [nvarchar](20) NULL,

 CONSTRAINT [PK_SSO_USER_LOGIN_LOG] PRIMARY KEY CLUSTERED

(

    [LOGIN_LOG_ID] 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

 

二、     新增人員記錄資料

SQL Script

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-18T09:01:00.000' AS DateTime), N'109001', N'192.168.1.123', N'PC-1', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-18T13:01:00.000' AS DateTime), N'109001', N'192.168.1.123', N'PC-1', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-18T17:01:00.000' AS DateTime), N'109001', N'192.168.1.123', N'PC-1', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-18T09:01:00.000' AS DateTime), N'109002', N'192.168.1.168', N'PC-2', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-19T09:01:00.000' AS DateTime), N'109001', N'192.168.1.123', N'PC-1', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-20T09:01:00.000' AS DateTime), N'109001', N'192.168.1.123', N'PC-1', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-19T09:01:00.000' AS DateTime), N'109002', N'192.168.1.168', N'PC-2', N'OO')

GO

INSERT [dbo].[USER_LOGIN_LOG] ([LOGIN_TIME], [USER_ACCOUNT], [CLIENT_IP], [DEVICE_KIND], [CNAME])

VALUES (CAST(N'2020-12-20T09:01:00.000' AS DateTime), N'109002', N'192.168.1.168', N'PC-2', N'OO')

GO

 

三、     Stored Procedure 撰寫方式

1.       語法分段方式作法

SQL Script

       DECLARE @EmployeeID varchar(20) ='109001',   --員工編號

                     @StartDate datetime ='',  --起日

                     @EndDate datetime =''    --訖日

 

    IF @EmployeeID =''

    BEGIN

       SELECT *

       FROM USER_LOGIN_LOG WITH(NOLOCK)

       WHERE LOGIN_TIME BETWEEN @StartDate AND DATEADD(DAY, +1, @EndDate)

    END

    ELSE

    BEGIN

       SELECT *

       FROM USER_LOGIN_LOG WITH(NOLOCK)

       WHERE USER_ACCOUNT = @EmployeeID

    END

 

執行SQL Script結果

 

2.       語法組成方式作法

SQL Script

       DECLARE @EmployeeID varchar(20) ='109001',   --員工編號

                     @StartDate datetime ='',  --起日

                     @EndDate datetime =''    --訖日

 

    DECLARE @query NVARCHAR(MAX) = N'

       SELECT *

       FROM USER_LOGIN_LOG WITH(NOLOCK) '

 

    IF @EmployeeID =''

       SET @query += 'WHERE LOGIN_TIME BETWEEN '''+@StartDate+''' AND DATEADD(DAY, +1, '''+@EndDate+''')';

    ELSE

       SET @query += 'WHERE USER_ACCOUNT = ''' + @EmployeeID + '''';

 

    EXEC sp_executesql @query

 

執行SQL Script結果

 

3.       建議作法

SQL Script

       DECLARE @EmployeeID varchar(20) ='109001',   --員工編號

                     @StartDate datetime ='',  --起日

                     @EndDate datetime =''    --訖日

SELECT *

                     FROM USER_LOGIN_LOG WITH(NOLOCK)

WHERE LOGIN_TIME between @StartDate AND DATEADD(DAY, +1, @EndDate) or isnull(@EmployeeID, USER_ACCOUNT) = USER_ACCOUNT

 

 

總結:本篇介紹用帳號或日期區間進行查詢,實際上可能會加上帳號且日期區間,如果再用前面二種作法,這樣Stored Procedure的語法又會更長,當SQL Script越來越複雜護維就不容易,設計人員也要考慮後面的人員維護方便性,上述的建議作法是不是讓SQL Script更簡潔且易維護,希望身為開發人員的你,能為你的作品提供更好的品質。

 

4. 參考來源

廖尉村 Johnson Liao