Why doesnt this sort my resultset on the subject column?
For the sortExpression parameter I tried filling in 'title', 'e.title', 'e.title asc', but none of them seem to work!
create PROCEDURE [dbo].[_getCompanyEmailsByIdSorted]
@startRowIndex int
,@maximumRows int
,@companyID int
,@sortExpression nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
--set the default sortfield
if @sortExpression=''
set @sortExpression='e.title'
SELECT *
FROM
(select ROW_NUMBER() OVER (ORDER BY @sortExpression) as RowNum,e.*
FROM emails e
INNER JOIN campaigns camp ON camp.uuid = e.campaignUUID
INNER JOIN companies c ON c.id = camp.companyID
WHERE camp.companyID = @companyid
) as Info
WHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)
END
DECLARE @RC int
DECLARE @startRowIndex int
DECLARE @maximumRows int
DECLARE @companyID int
DECLARE @sortExpression nvarchar(20)
set @startRowIndex=0
set @maximumRows=100
set @companyID=2
set @sortExpression='subject'
Peter Smith
Contributor
4605 Points
2109 Posts
dynamic sortfield not working
Dec 17, 2008 08:24 PM|LINK
Why doesnt this sort my resultset on the subject column?
For the sortExpression parameter I tried filling in 'title', 'e.title', 'e.title asc', but none of them seem to work!
create PROCEDURE [dbo].[_getCompanyEmailsByIdSorted]
@startRowIndex int
,@maximumRows int
,@companyID int
,@sortExpression nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
--set the default sortfield
if @sortExpression=''
set @sortExpression='e.title'
SELECT *
FROM
(select ROW_NUMBER() OVER (ORDER BY @sortExpression) as RowNum,e.*
FROM emails e
INNER JOIN campaigns camp ON camp.uuid = e.campaignUUID
INNER JOIN companies c ON c.id = camp.companyID
WHERE camp.companyID = @companyid
) as Info
WHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)
END
DECLARE @RC int
DECLARE @startRowIndex int
DECLARE @maximumRows int
DECLARE @companyID int
DECLARE @sortExpression nvarchar(20)
set @startRowIndex=0
set @maximumRows=100
set @companyID=2
set @sortExpression='subject'
EXECUTE @RC = [mycampaign].[dbo].[_getCompanyEmailsByIdSorted]
@startRowIndex
,@maximumRows
,@companyID
,@sortExpression
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: dynamic sortfield not working
Dec 17, 2008 09:23 PM|LINK
Try this:
...... ORDER
BYCASE
WHEN @sortExpression='subject' THEN subject END,CASE
WHEN @sortExpression='title' THEN title END)
as RowNumFormat your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: dynamic sortfield not working
Dec 18, 2008 02:15 AM|LINK
Hi,
So U want to use Dynamic Order BY In Static Query
So U can maintain order by as for example in this way
I am Showin u a test example
.......order by
case when @mostvoted is not null then vote.Votes end desc,
case when @highestfeedback is not null then cte.feedback end desc,
case when @browselatest is not null then mod.submissiondate end desc,
case when @mostviewed is not null then mod.NoOfViews end desc
U can Refer This Link here U will find the conversion to Dynamic where by and dynamic order by clause
U can Get Help from that
http://forums.asp.net/t/1361436.aspx?PageIndex=2
Peter Smith
Contributor
4605 Points
2109 Posts
Re: dynamic sortfield not working
Dec 18, 2008 07:43 AM|LINK
I know this is a possible solution, but see this article here by Scott Mitchell:
http://aspnet.4guysfromrolla.com/articles/032206-1.aspx#
Far easier and less maintenance sensitive...how should I implement that?
Thanks!