El procedimiento almacenado de T-SQL con sorting y pagination habilitado no funciona correctamente

Hola chicos, estoy usando el siguiente código

ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stonetworking 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 

Cuando estoy ejecutando el process almacenado usando el siguiente 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 

Obtengo estos resultados que no están orderados.

 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 

¿Puedes decirme qué más se requiere?


Lo he intentado, pero también está dando un resultado sin clasificar en el plano

 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 

Reemplace su procedimiento con esto:

 ALTER PROCEDURE [dbo].[usp_get_all_groups] -- Add the parameters for the stonetworking 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 

No puede asignar dinámicamente asc y desc a una expresión no dinámica.

Parece que existe la idea errónea de que la expresión

 ORDER BY UserGroups._id + ' DESC' 

hará que SQL Server aplique un order descendente. Lo que realmente hace es simplemente agregar la cadena literal "DESC" al valor de la columna y luego orderar el resultado en order ascendente.

Necesita crear dinámicamente toda la instrucción SELECT en el procedimiento, aplicando ORDER BY dos veces como se bosquejó en la respuesta de marc_s, y ejecutar la instrucción usando sp_executesql. sp_executesql también te permite pasar los parameters @.

estás perdido

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

También verifique que este enlace pueda ayudarlo

No está orderando su instrucción SELECT …. ni el SELECT interno de UserGroups tiene un ORDER BY , ni el SELECT externo …. ¡usted necesita proporcionar el ORDER BY en el SELECT , también! (No solo en la ROW_NUMBER() function ROW_NUMBER() )

 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 

El ORDER BY dentro de la cláusula OVER() solo se usa para calcular los valores de Rowno ordera el set de datos resultante.

Quizás esto ayude (si tanto _id como GroupCode son del mismo tipo):

 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