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