Reputation: 5543
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 am executing the stored proc 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
I am getting these results which is 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 have tried this, but it is also giving plane 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
Upvotes: 4
Views: 6818
Reputation: 44356
Replace your procedure with this:
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 can't dynamically assign asc and desc to a non-dynamic expression.
Upvotes: 10
Reputation: 31249
Maybe this will help (if both _id and GroupCode are 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
Upvotes: 1
Reputation: 37225
There seems to be the misconception that the expression
ORDER BY UserGroups._id + ' DESC'
will cause SQL Server to apply a descending order. What it really does is simply append the string literal " DESC" to the column value and then sort the result in ascending order.
You need to dynamically create the whole SELECT statement in the procedure, applying the ORDER BY twice as sketched in marc_s' answer, and execute the statement using sp_executesql. sp_executesql also allows you to pass the @ parameters.
Upvotes: 4
Reputation: 755541
You're not ordering your SELECT
statement .... neither the inner SELECT
from UserGroups
has an ORDER BY
, nor does the outer SELECT
.... you need to provide the ORDER BY
on the SELECT
, too! (not just in your ROW_NUMBER()
function's OVER()
clause)
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
The ORDER BY
inside the OVER()
clause is only used to compute the Row
values - it does not order the resulting data set.
Upvotes: 1
Reputation: 1167
you are missing
@pOrderBy varchar(20) because of this your @pOrderBy has only one char 'G'
also check this link may help you
Upvotes: 1