ALTER PROCEDURE [dbo].[spSchool]
@Id nvarchar(20),
@name nvarchar(200),
@jurisdictionId nvarchar(20),
@regionId nvarchar(20),
@provinceId nvarchar(20),
@pagesize tinyint = 10,
@pageindex int =1,
@totalrecords int output
AS
BEGIN
SET NOCOUNT ON;
declare @upperband int, @lowerband int;
select @totalrecords = count(1)
from ms_school ms
left join BasicJurisdiction j on j.JurisdictionID = ms.JurisdictionID
left join BasicProvince p on p.ProvinceID = ms.ProvinceID
--อยากจะ if ตรง where ถ้ามีแค่ @Id ก็หาแค่ @Id เพราะใช้ and ทั้งหมดแล้วดึงข้อมูลไม่ได้ พอใช้ or ค่าออกมาเพี้ยน
where (ms.schoolid like '%'+@Id+'%') and (ms.schoolname like '%'+@name+'%') or (ms.JurisdictionID like '%'+@jurisdictionId+'%') or (p.RegionID like '%'+@regionId+'%') or (ms.ProvinceID like '%'+@provinceId+'%')
set @lowerband = (@pageindex - 1) * @pagesize;
set @upperband = (@pageindex * @pagesize) + 1;
with temp_school_by_page as (
select top(@pageindex * @pagesize) row_number() over (order by ms.SchoolID) as rownumber
,ms.*, j.JurisdictionName
from ms_school ms
left join BasicJurisdiction j on j.JurisdictionID = ms.JurisdictionID
left join BasicProvince p on p.ProvinceID = ms.ProvinceID
where (ms.schoolid like '%'+@Id+'%') and (ms.schoolname like '%'+@name+'%') or (ms.JurisdictionID like '%'+@jurisdictionId+'%') or (p.RegionID like '%'+@regionId+'%') or (ms.ProvinceID like '%'+@provinceId+'%')
)select * from temp_school_by_page
where rownumber > @lowerband and rownumber < @upperband;
END
น่าจะใช้ CASE WHEN <เงื่อนไข> THEN <????>
ตัวอย่างเช่น
------------------------ Code (SQL)
DECLARE
@Id nvarchar(20);
@name nvarchar(200),
SELECT
column1,
column2
FROM
viewWhatever
WHERE
CASE
WHEN LEN(@Id)>0 THEN (ms.schoolid like '%'+@Id+'%')
WHEN LEN(@Id)>0 AND LEN(@name)>0 THEN (ms.schoolid like '%'+@Id+'%') AND (ms.schoolname like '%'+@name+'%')
ELSE (ms.schoolid is not null)
END = 1
Load balance : Server 02
