Order and subquery
I'm having problems with the underlying stored procedure, unfortunately I'm not an sql expert, I'm trying to search for clauses on two parameters @OrderBy
and @SortOrder
however the result is sorted only by the subquery result. For example:
exec [dbo].[GetTest2] @Page=0,@Limit=150,@OrderBy=N'NetworkName',@SortOrder=N'asc'
works as expected, but
exec [dbo].[GetTest2] @Page=0,@Limit=15,@OrderBy=N'NetworkName',@SortOrder=N'asc'
doesn't work because the subquery doesn't return all records.
how can i sort across the entire recordset?
USE [MyContext]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[GetTest2]
@Page int = 0,
@Limit int = 10,
@OrderBy nvarchar(50) = 'OfferId',
@Name nvarchar(100) = NULL,
@SortOrder nvarchar(50) = 'asc'
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY @OrderBy) AS ROWNUMBER,
COUNT(*) OVER() AS [Total_Rows],
a.Name AS NetworkName,
o.*
FROM offers AS o
left outer join AffiliateNetworks AS a ON o.NetworkId = a.Id
WHERE (@Name IS NULL OR o.Name LIKE '%' + @Name + '%' )) AS Paged
WHERE
Paged.ROWNUMBER > @Limit * @Page AND
Paged.ROWNUMBER <= (@Limit * @Page) + @Limit
order by
case
when @SortOrder <> 'asc' then NULL
when @OrderBy = 'Name' then Name
end ASC,
case
when @SortOrder <> 'asc' then NULL
when @OrderBy = 'NetworkName' then NetworkName
end ASC,
case
when @SortOrder <> 'desc' then NULL
when @OrderBy = 'Name' then Name
end DESC,
case
when @SortOrder <> 'desc' then NULL
when @OrderBy = 'NetworkName' then NetworkName
end DESC
END
source to share
You might want to add sorting to ROW_NUMBER to keep paging persistent. I've wrapped the sub request in a CTE to make it a little easier to read.
ALTER PROCEDURE [dbo].[GetTest2]
@Page int = 0,
@Limit int = 10,
@OrderBy nvarchar(50) = 'OfferId',
@Name nvarchar(100) = NULL,
@SortOrder nvarchar(50) = 'asc'
AS
BEGIN
SET NOCOUNT ON;
;WITH Paged AS (
SELECT ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @SortOrder = 'ASC'
THEN (CASE @OrderBy WHEN = 'Name' THEN Name WHEN 'NetworkName' THEN a.Name END) END ASC,
CASE WHEN @SortOrder = 'DESC'
THEN (CASE @OrderBy WHEN = 'Name' THEN Name WHEN 'NetworkName' THEN a.Name END) END DESC)
) AS ROWNUMBER,
COUNT(*) OVER() AS [Total_Rows],
a.Name AS NetworkName,
o.*
FROM offers AS o
left outer join AffiliateNetworks AS a ON o.NetworkId = a.Id
WHERE (@Name IS NULL OR o.Name LIKE '%' + @Name + '%' )
)
SELECT *
FROM Paged
WHERE ROWNUMBER - (@Limit * @Page) BETWEEN 1 AND @Limit
ORDER BY ROWNUMBER
END
GO
As mentioned in the comments, ROW_NUMBER
might not be the best swap construct in later versions of SQL Server. If you are using at least 2012 or more, you can use OFFSET
and FETCH NEXT
for its paging. Then your request will become something like this:
SELECT
COUNT(*) OVER() AS [Total_Rows],
CASE @OrderBy WHEN = 'Name' THEN o.Name WHEN 'NetworkName' THEN a.Name END AS SortName,
a.Name AS NetworkName,
o.*
FROM offers AS o
left outer join AffiliateNetworks AS a ON o.NetworkId = a.Id
WHERE (@Name IS NULL OR o.Name LIKE '%' + @Name + '%' )
ORDER BY
CASE WHEN @SortOrder = 'ASC' THEN SortName ELSE NULL END ASC,
CASE WHEN @SortOrder = 'DESC' THEN SortName ELSE NULL END DESC
OFFSET (@Page * @Limit) ROWS
FETCH NEXT @Limit ROWS ONLY;
source to share
Try the following:
order by
case
when @OrderBy = 'Name' and @SortOrder = 'asc' then Name
when @OrderBy = 'NetworkName' and @SortOrder = 'asc' then NetworkName
end ASC,
case when @OrderBy = 'Name' and @SortOrder = 'desc' then Name
when @OrderBy = 'NetworkName' and @SortOrder = 'desc' then NetworkName
end DESC
source to share