June 12, 2009 at 3:46 am
HI,
I have the below SP.I want to sort the data according to @Sortby that is passed...
Here is my Code,
ALTER PROCEDURE GetInactiveUserdetail
@PageIndex int,
@NoOfRows int,
@SortBy varchar(20),
@UsersCount int output
AS
DECLARE @startRowIndex int;
BEGIN
SELECT
@UsersCount = isnull(count(*), 0)
FROM
aspnet_Membership INNER JOIN
UserActivationDetails ON aspnet_Membership.UserId = UserActivationDetails.UserId INNER JOIN
UserGeneral ON aspnet_Membership.UserId = UserGeneral.UserId
WHERE
(UserActivationDetails.ActivationStatus = 'False') AND (GETDATE() >= aspnet_Membership.CreateDate + 7) AND (aspnet_Membership.UserId NOT IN
(SELECT UserId
FROM ActivationReminders))
SET @startRowIndex = (@PageIndex * @NoOfRows) + 1;
WITH ContentEntries AS
(
SELECT ROW_NUMBER() OVER (ORDER BY m.CreateDate) AS ROW,
m.UserId, m.Email,m.CreateDate,U.NickName, U.FirstName, U.LastName, U.ImageThumbnailURL, U.Gender, U.RegistrationType,Days=datediff(dd,m.CreateDate, getdate())
FROM aspnet_Membership m INNER JOIN
UserActivationDetails a ON m.UserId = a.UserId INNER JOIN
UserGeneral U ON m.UserId = U.UserId
WHERE a.ActivationStatus = 'False' AND (GETDATE() >= m.CreateDate + 7) AND (m.UserId NOT IN
(SELECT UserId
FROM ActivationReminders))
)
SELECT
UserId, Email, CreateDate, NickName, FirstName, LastName,
ImageThumbnailURL, Gender, RegistrationType,Days
FROM
ContentEntries
WHERE
ROW BETWEEN @startRowIndex AND @startRowIndex + @NoOfRows - 1
ORDER BY
@SortBy
END
But when i am trying to do this there is an Error,I think there is some other way to sort dynamically.. plz help
June 12, 2009 at 4:18 am
Of course.
Instead of select....order by @sortby
you execute this:
exec('select....order by '+@sortby)
June 12, 2009 at 5:01 am
You can use a case statement e.g.
...
ORDER BY
CASE @sortby
WHEN 'FirstName' THEN u.FirstName
WHEN 'LastName' THEN u.LastName
...
end
If you are going to provide the ability to sort on columns that have a different datatype you may end up with an error similar to :-
Conversion failed when converting the nvarchar value 'LastName' to data type int.
This is because SQL Server is deciding the datatype of the ORDER BY based on it's rules of precedence, and if it sees that one of the columns could be an integer, it assumes they are all integers. You will then have to explicitly cast all integers, dates etc to varchar e.g.
ORDER BY
CASE
...
WHEN 'FirstName' THEN u.FirstName
WHEN 'UserId' THEN convert(varchar(20),M.UserId)
June 12, 2009 at 6:18 am
Hi,
Thanks for the reply, the code below is working now,
But i have to sort all the datas in the table and then select according to pageIndex,but here first the datas according to the pageindex are fetched and then sorted,
ALTER PROCEDURE GetInactiveUserdetail
@PageIndex int,
@NoOfRows int,
@SortBy varchar(20),
@UsersCount int output
AS
DECLARE @startRowIndex int;
BEGIN
SELECT
@UsersCount = isnull(count(*), 0)
FROM
aspnet_Membership INNER JOIN
UserActivationDetails ON aspnet_Membership.UserId = UserActivationDetails.UserId INNER JOIN
UserGeneral ON aspnet_Membership.UserId = UserGeneral.UserId
WHERE
(UserActivationDetails.ActivationStatus = 'False') AND (GETDATE() >= aspnet_Membership.CreateDate + 7) AND (aspnet_Membership.UserId NOT IN
(SELECT UserId
FROM ActivationReminders))
SET @startRowIndex = (@PageIndex * @NoOfRows) + 1;
WITH ContentEntries AS
(
SELECT ROW_NUMBER() OVER (ORDER BY m.CreateDate) AS ROW,
m.UserId, m.Email,m.CreateDate,U.NickName, U.FirstName, U.LastName, U.ImageThumbnailURL, U.Gender, U.RegistrationType,Days=datediff(dd,m.CreateDate, getdate())
FROM aspnet_Membership m INNER JOIN
UserActivationDetails a ON m.UserId = a.UserId INNER JOIN
UserGeneral U ON m.UserId = U.UserId
WHERE a.ActivationStatus = 'False' AND (GETDATE() >= m.CreateDate + 7) AND (m.UserId NOT IN
(SELECT UserId
FROM ActivationReminders))
)
SELECT
UserId, Email, CreateDate, NickName, FirstName, LastName,
ImageThumbnailURL, Gender, RegistrationType,Days
FROM
ContentEntries
WHERE
ROW BETWEEN @startRowIndex AND @startRowIndex + @NoOfRows - 1
ORDER BY
CASE WHEN @SortBy='Email' THEN Email
WHEN @SortBy='Days' THEN CONVERT(varchar(50), Days)
WHEN @SortBy='FrstName' THEN FirstName
WHEN @SortBy='LstName' THEN LastName
END
END
June 12, 2009 at 6:49 am
Well, this won't work properly, when @SortBy='Days desc' or @SortBy='FrstName, days' or ....
Also, you're paging on m.CreateDate and sort within page using @SortBy.
Try something like this:
EXEC('select .... SELECT ROW_NUMBER() OVER (ORDER BY '+@SortBy+') AS ROW,.... ORDER BY '+@SortBy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply