SQL Revamp / Tuning plan
Writing a TSQL for interactivity with data that stored inside the database would a one of backend engineers' daily stuff. T-SQL would be easily to create and build. Write/design a good structure and quality styling one would be somewhat tough as you might think.
Below was a example SQL script that violate the quality gate scan under the engine of SQL Enlight.
create procedure [dbo].[Usp_WF_XXXXX_List]
@FormID varchar(10)
, @Region varchar(6)
, @Year varchar(4)
, @Author varchar(50)
, @Search varchar(50)
, @SortColumn varchar(40)
, @SortOrder varchar(4)
, @Processing bit = null
, @Page int
, @Count int
, @TotalPage int output
as
begin
set nocount on;
select [FormSerialNumber]
, [ASComposedDate]
, [Region]
, [ASAuditor]
, [ASStatus]
, [ASKingstonPN]
, [ASStartDate]
, [ASTargetDate]
, [ASEndDate]
, concat([FormSerialNumber], [Region], [ASAuditor], [ASStatus], [ASKingstonPN]) as [Search]
into [#tempCategory]
from [dbo].[V_WF_YYYYY_List] as [L]
inner join [dbo].[WF_FormListStatus] as [S]
on
[S].[FormID] = @FormID and [L].[ASStatus] = [S].[Status]
and
(
coalesce(@Processing, [Processing]) = [Processing]
)
where (
-- SA106
@Region = '' or [Region] = @Region
) and
(
-- SA106, SA0027
@Year = '' or year([ASComposedDate]) = @Year
) and
(
-- SA106
@Author = '' or [ASAuditor] = @Author
);
select [FormSerialNumber]
, [ASComposedDate]
, [Region]
, [ASAuditor]
, [ASStatus]
, [ASKingstonPN]
, [ASStartDate]
, [ASTargetDate]
, [ASEndDate]
into [#tempFormList]
from [#tempCategory]
-- SA106
where @Search = '' or [Search] like '%' + @Search + '%';
select @TotalPage =
(
-- SA0251, SA0081, SA0168
select ceiling(cast(count(1) as decimal) / @Count)
from [#tempFormList]
);
if @SortColumn = ''
begin
set @SortColumn = 'ASComposedDate';
end;
if @SortOrder = ''
begin
set @SortOrder = 'DESC';
end;
declare @sql nvarchar(4000);
declare @parameters nvarchar(60);
set @parameters = N'@offset INT, @rows INT';
declare @tmp_offset int;
set @tmp_offset = @Count * ( @Page - 1 );
set @sql =
N'SELECT [FormSerialNumber], [ASComposedDate], [Region], [ASAuditor], [ASStatus], [ASKingstonPN], [ASStartDate], [ASTargetDate], [ASEndDate]
FROM [#tempFormList]
ORDER BY ' + @SortColumn + N' ' + @SortOrder + N' OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY;';
exec [sys].[sp_executesql] @stmt = @sql
, @params = @parameters
, @offset = @tmp_offset
, @rows = @Count;
if object_id('tempdb..#tempCategory') is not null
begin
drop table [#tempCategory];
end;
if object_id('tempdb..#tempFormList') is not null
begin
drop table [#tempFormList];
end;
return 0;
end;
goThe procedure just less than 130 lines but with some issues:
Let solved step by step:
-- using case/when to replac or condition insdie where for SA106
[Region] = case
when @Region = '' then
[Region]
else
@Region
end-- using case/when to replac or condition insdie where for SA106
-- using adddate to enclosed 'yyyy-01-01' <= [ASComposedDate] < 'yyyy+1(year)-01-01' @year for SA0027
and ( case
when @Year = '' then
1
when [ASComposedDate] >= @Year + '-01-01' and [ASComposedDate] < dateadd(year, 1, @Year + '-01-01') then
1
else
@Year
end
) = 1
-- using colalesce to ensure return first nonnull recrod for SA0251
-- using decimal(10, 2) to fix the precesion and scale for SA0081
-- using nullif to reslove divide zero issue for SA0168
select @TotalPage = coalesce(ceiling(cast(count(1) as decimal(10, 2)) / nullif(@Count, 0)), 0)
from [#tempFormList];