The stored procedure is T-SQL with sorting and paging activated incorrectly

Question

Hi guys, I am using the following code

ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stored procedure here @pStartIndex smallint, @pPageSize tinyint, @pOrderBy varchar AS BEGIN SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END ) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize END 

When I execute a saved process using the following command

 DECLARE @return_value int EXEC @return_value = [dbo].[usp_get_all_groups] @pStartIndex = 0, @pPageSize = 15, @pOrderBy = N'GroupCode ASC' SELECT 'Return Value' = @return_value Usp_get_all_groups] DECLARE @return_value int EXEC @return_value = [dbo].[usp_get_all_groups] @pStartIndex = 0, @pPageSize = 15, @pOrderBy = N'GroupCode ASC' SELECT 'Return Value' = @return_value 

I get these results are not sorted.

 Row _id GroupCode Description Type IsActive 1 1 CS2009 CS 2009 Batch S 1 2 2 IT2009 IT 2009 Batch S 1 3 3 ME2009 ME 2009 Batch S 1 4 4 EC2009 EC 2009 Batch S 1 5 5 EE2009 EE 2009 Batch S 1 6 8 CS_F CS Faculties F 1 7 9 IT_F IT Faculties F 1 8 10 ME_F ME Faculties F 1 9 11 EC_F EC Faculties F 1 10 12 EE_F EE Faculties F 1 11 13 BSC_F Basic Science Faculties F 1 12 14 Accounts Accounts A 1 13 15 Mgmt Management M 1 14 16 Lib Library B 1 15 17 TnP Training & Placement T 1 

Can you tell me what else is required?

---

I tried this, but it also gives a flat unsorted result

 SELECT GroupTable._id, GroupTable.GroupCode, GroupTable.Type, GroupTable.Description FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN CONVERT(varchar(20), '_id ASC') WHEN @pOrderBy='GroupId DESC' THEN CONVERT(varchar(20), '_id DESC') WHEN @pOrderBy='GroupCode ASC' THEN CONVERT(varchar(20), @pOrderBy) WHEN @pOrderBy='GroupCode DESC' THEN CONVERT(varchar(20), @pOrderBy) END ) AS Row, * FROM UserGroups) AS GroupTable WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize Select COUNT(*) as TotalRows from UserGroups where IsActive= 1 

Answer 1

Replace the procedure as follows:

 ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stored procedure here @pStartIndex smallint, @pPageSize tinyint, @pOrderBy varchar(15) AS BEGIN SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id END ASC, CASE WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id END DESC, CASE WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode END ASC, CASE WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode END DESC) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY Row END BY ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stored procedure here @pStartIndex smallint, @pPageSize tinyint, @pOrderBy varchar(15) AS BEGIN SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id END ASC, CASE WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id END DESC, CASE WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode END ASC, CASE WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode END DESC) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY Row END ' THEN UserGroups._id END DESC, ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stored procedure here @pStartIndex smallint, @pPageSize tinyint, @pOrderBy varchar(15) AS BEGIN SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id END ASC, CASE WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id END DESC, CASE WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode END ASC, CASE WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode END DESC) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY Row END ' THEN UserGroups.GroupCode END ASC, ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stored procedure here @pStartIndex smallint, @pPageSize tinyint, @pOrderBy varchar(15) AS BEGIN SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id END ASC, CASE WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id END DESC, CASE WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode END ASC, CASE WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode END DESC) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY Row END 

You cannot dynamically assign asc and desc to a non-dynamic expression.

Answer 2

It seems like the misconception that the expression

 ORDER BY UserGroups._id + ' DESC' 

will cause SQL Server to use descending order. In fact, it just adds a string literal "DESC" to the value of the column, and then sort the results in ascending order.

You need to dynamically create the entire SELECT statement in the procedure, applying ORDER BY twice as indicated in marc_s answer, and execute the statement using sp_executesql. sp_executesql also allows you to pass @ parameters.

Answer 3

you are missing

 @pOrderBy varchar(20) because of this your @pOrderBy has only one char 'G' 

also check that the link can help you

Answer 4

You do not book your operator SELECT .... no inner SELECT from UserGroups has no ORDER BY , or outer SELECT .... you need to provide the ORDER BY in the SELECT too! (not only in your ROW_NUMBER() function of OVER() )

 SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row >= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END THEN UserGroups.GroupCode + 'ASC' SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row >= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END THEN UserGroups._id + 'ASC' SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row >= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END THEN UserGroups._id + 'DESC' SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row >= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END THEN UserGroups.GroupCode + 'DESC' SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END) AS Row, * FROM UserGroups) AS StudentsWithRowNumbers WHERE Row >= @pStartIndex AND Row <= @pStartIndex + @pPageSize ORDER BY CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC' WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC' WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC' WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC' END 

ORDER BY within OVER() - only used to calculate the values of Row - it does not order the result dataset.

Answer 5

Maybe this will help (if both _id and GroupCode are of the same type):

 DECLARE @pOrderBy VARCHAR(100), @pStartIndex smallint, @pPageSize tinyint SET @pOrderBy='GroupId DESC' SET @pStartIndex=0 SET @pPageSize=15 SELECT GroupTable._id, GroupTable.GroupCode, GroupTable.Type, GroupTable.Description FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE @pOrderBy WHEN 'GroupId ASC' THEN UserGroups._id WHEN 'GroupCode ASC' THEN UserGroups.GroupCode END ASC, CASE @pOrderBy WHEN 'GroupId DESC' THEN UserGroups._id WHEN 'GroupCode DESC' THEN UserGroups.GroupCode END DESC ) AS Row, * FROM UserGroups) AS GroupTable WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize BY DECLARE @pOrderBy VARCHAR(100), @pStartIndex smallint, @pPageSize tinyint SET @pOrderBy='GroupId DESC' SET @pStartIndex=0 SET @pPageSize=15 SELECT GroupTable._id, GroupTable.GroupCode, GroupTable.Type, GroupTable.Description FROM (SELECT ROW_NUMBER() OVER (ORDER BY CASE @pOrderBy WHEN 'GroupId ASC' THEN UserGroups._id WHEN 'GroupCode ASC' THEN UserGroups.GroupCode END ASC, CASE @pOrderBy WHEN 'GroupId DESC' THEN UserGroups._id WHEN 'GroupCode DESC' THEN UserGroups.GroupCode END DESC ) AS Row, * FROM UserGroups) AS GroupTable WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize