Edit Question - KnowledgeHub

Title Body

I am creating below stored procedure. declare @PageNum as Int declare @PerPageResult as Int declare @StartDate as varchar(25) declare @EndDate as varchar(25) declare @SortType as Varchar(50) declare @SortDirection as Varchar(4) set @PageNum=1 set @PerPageResult=20 set @StartDate='2008-02-08' set @EndDate='2015-02-08' set @SortType='RegDate' set @SortDirection='Desc' declare @Temp Table(RowNum int, RegDate Date, Registered int, Female int, Male int, [Join] int, Rebill int, TotalPointsEarned int, Expire int) declare @sort varchar(50) Insert into @Temp Select ROW_NUMBER() over (order by @SortType+' '+@SortDirection) As RowNum, * From ( SELECT CAST(m.registrationdate AS Date) as RegDate, count(m.id) Registered, count(CASE WHEN m.gender='F' then 'F' end) As Female, count(CASE WHEN m.gender='M' then 'M' end) As Male count(CASE WHEN p.paymenttransactiontype='N' then 'N' end) As [Join], count(CASE WHEN p.paymenttransactiontype='R' then 'R' end) As Rebill, count(m.tokensearned) As TotalPointsEarned, count(CASE WHEN p.paymenttransactiontype='E' then 'E' end) As Expire from member m join payment p on m.id=p.id_member join user_role u on u.member_id=m.id where u.role_id <> 3 and CAST(m.registrationdate AS Date) > @StartDate and CAST(m.registrationdate AS Date) < @EndDate GROUP BY CAST(m.registrationdate AS Date) ) as aa Select * from @Temp Where RowNum>((@PageNum-1)*@PerPageResult) and RowNum<=@PerPageResult * @PageNum Order by @SortType+' '+@SortDirection In above when i pass the Order by clause dynamically, its not sorting the data properly but when i write column name explicitly, it works fine. Might be its taking @SortType+' '+@SortDirection as varchar rather than Date I tried writing Order by case when (@Sort='RegDate' and @SortDirection='Desc') Then RegDate End Desc</c

Tags (comma-separated) Cancel