SQL Server-預存程序-動態分頁、排序、過濾
前言
目前寫的系統，部份查詢程式會呼叫預存程序，進行較複雜的運算來取得資料，但由於回傳的資料過大，導致雖然在程式端進行了分頁處理，RAM還是會因資料已載入而暴增。
而程式介面的功能設計上，使用者可以在查詢出來的列表，再進行排序、動態過濾、排序及分頁..等，這些功能若要在預存程序處理的話，就要將SQL改成組字串的方式。
而程式介面的功能設計上，使用者可以在查詢出來的列表，再進行排序、動態過濾、排序及分頁..等，這些功能若要在預存程序處理的話，就要將SQL改成組字串的方式。
這樣原本就已很複雜的語法會變得更不好維護，在考慮先不修預存程序前提下，改用另一個預存程序動態組字串來呼叫本來的預存程序的寫法來達到相同的功能。
SQL
建立Table
CREATE TABLE [dbo].[DemoUser](
[USER_NO] [varchar](8) NOT NULL,
[USER_NAME] [varchar](20) NULL
)
塞資料
DECLARE @I AS INT
DECLARE @MAX AS INT
DECLARE @STR_I AS VARCHAR(30)
SET @I=1
SET @MAX=1000000
WHILE (@I<@MAX)
BEGIN
SET @STR_I=CONVERT(varchar(10),@I)
SET @I=@I+1
END
預存-1 查詢資料
create Procedure [dbo].[Proc_GetUsers]
@USER_NO VARCHAR(20)
AS
SELECT * FROM DemoUser where USER_NO LIKE '%'+@USER_NO+'%'
預存-2 動態SQL
/*--------------------------------------------------
description:動態分頁、排序、過濾範例
--------------------------------------------------
exec Proc_GetUsers_Dynamic
@CurrentPage =1
,@PageSize =50
,@FilterSql='AND USER_NAME LIKE ''1%'''
,@SortSql='USER_NO ASC'
,@USER_NO='10'
--------------------------------------------------*/
CREATE Procedure [dbo].[Proc_GetUsers_Dynamic]
@CurrentPage INT /*目前所在的頁數*/
,@PageSize INT /*每頁的固定筆數*/
,@FilterSql varchar(400)
,@SortSql varchar(50)
,@USER_NO VARCHAR(20)
AS
--For Entity Framework Designer產生結果型別
IF @CurrentPage is null
BEGIN
DECLARE @tempTable TABLE(
[USER_NO] [varchar](8),
[USER_NAME] [varchar](20)
)
select * from @tempTable
return
END
--主要動態SQL
DECLARE @sql NVARCHAR(MAX)
SET @sql = '
DECLARE @tempTable TABLE(
[USER_NO] [varchar](8),
[USER_NAME] [varchar](20)
)
insert into @tempTable
exec dbo.Proc_GetUsers @USER_NO
SELECT TotalCount = COUNT(1) OVER (), * FROM @tempTable
where 1=1 '+@FilterSql+'
ORDER BY '+@SortSql+'
OFFSET(@CurrentPage - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
'
EXECUTE sp_executesql @sql,N'@CurrentPage int,@PageSize int,@USER_NO varchar(8)',@CurrentPage,@PageSize,@USER_NO
雖然在效能上不是最佳的解決方法，但在目前為兼顧效能及開發的速度下，這不失為一個可用的解決方案。
