I have below stored procedure in sql server 2016, its working fine there.
Now I need to create the same sp in sql 2008, now I am getting error :
Msg 102, Level 15, State 1, Procedure GetEmployees, Line 41 [Batch Start Line 0] Incorrect syntax near 'OFFSET'. Msg 153, Level 15, State 2, Procedure GetEmployees, Line 42 [Batch Start Line 0] Invalid usage of the option NEXT in the FETCH statement.
How to modify the same proc so that it can run over sql 2008 as well.
--dbo.GetEmployees '',2,2
CreatePROCEDURE [dbo].GetEmployees
(
@SearchValue NVARCHAR(50) = '',
@PageNo INT = 0,
@PageSize INT = 10,
@SortColumn NVARCHAR(20) = 'Name',
@SortOrder NVARCHAR(20) = 'ASC'
)
AS BEGIN
SET NOCOUNT ON;
if @PageNo<0 set @PageNo=0
set @PageNo=@PageNo+1
SET @SearchValue = LTRIM(RTRIM(@SearchValue))
Set @SearchValue= nullif(@SearchValue,'')
; WITH CTE_Results AS
(
SELECT EmployeeID, Name, City from tblEmployee
WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')
ORDER BY
CASE WHEN (@SortColumn = 'EmployeeID' AND @SortOrder='ASC')
THEN EmployeeID
END ASC,
CASE WHEN (@SortColumn = 'EmployeeID' AND @SortOrder='DESC')
THEN EmployeeID
END DESC,
CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC')
THEN Name
END ASC,
CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC')
THEN Name
END DESC,
CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC')
THEN City
END ASC,
CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC')
THEN City
END DESC
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
),
CTE_TotalRows AS
(
select count(EmployeeID) as MaxRows from tblEmployee WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')
)
Select MaxRows TotalRecords, t.EmployeeID, t.Name, t.City,t.Department,t.Gender from dbo.tblEmployee as t, CTE_TotalRows
WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.EmployeeID = t.EmployeeID)
OPTION (RECOMPILE)
END
You need a row_number() window function and in the OVER section you want to put your entire sorting expression. Note that I've created another CTE for readability, but you could get the same thing done with just a subquery.
Formatted code for the SELECT statement would be the following:
WITH CTE_Rownums AS (
SELECT
EmployeeID,
Name,
City,
row_number() over ( ORDER BY ... ) as rn -- put your entire order by here
FROM tblEmployee
WHERE
@SearchValue IS NULL
OR Name LIKE '%' + @SearchValue + '%'
), CTE_Results AS (
SELECT EmployeeID, Name, City
FROM CTE_Rownums
WHERE
(rn > @PageSize * (@PageNo - 1)
AND (rn <= @PageSize * @PageNo)
ORDER BY rn
), CTE_TotalRows AS (
SELECT count(EmployeeID) as MaxRows
FROM tblEmployee
WHERE
@SearchValue IS NULL
OR Name LIKE '%' + @SearchValue + '%'
)
SELECT MaxRows TotalRecords, t.EmployeeID, t.Name, t.City,t.Department,t.Gender
FROM dbo.tblEmployee as t
CROSS JOIN CTE_TotalRows
WHERE EXISTS (
SELECT 1
FROM CTE_Results
WHERE CTE_Results.EmployeeID = t.EmployeeID
)
OPTION (RECOMPILE)
In the last SELECT I've replaced comma separated where clause with CROSS JOIN.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments