sql server 2008 - Sorting a row limited SQL query with stored procedures -

I am using a stored procedure that tries to get some lines before a sorted SQL query Is where the input parameter specifies the number of rows and the sorting criteria to get.

I have succeeded in recovering the first few lines correctly, but when I try to sort the results, then they are the default criteria (primary key).

So I was wondering if anyone could see it? Get the SET QUOTED_IDENTIER OFF switch change process [dbo]

  USE [database] Get SET ANSI_NULLS OFF. [X0] @ Username nvarchar (50), @sortBy varchar (50), @sortDirection varchar (4), @startRow integer, @endRow integer as also by case (as compared to selection ROW_NUMBER () When @ sortBy = 'datecreate' then 'datecreate' when @ sortby = 'id' then 'id' when @ sortby = 'displayname' then 'displayname' is the end, when the case is @ sortDirection = 'ASC' then 'ASC' when @ SortDirection = 'description of' then 'digit' end) as ROnm, dB.X1. *, Dbo.x2. * FROM dbo.x1 INNER JOIN DB o.x2 dbo.x1.type = dbo.x2. Type where username = @ user name) select rownumber, *, displ Where as ayName DisplayName by command RowNumber between @startRow and @endRow I try to push forward the sorting criteria for external SQL queries Is of   
 
 
 You can do it like this:  
 ; With order (SELECT rnd = ROW_NUMBER ()) (by datecreate by order), rni = ROW_NUMBER () ID (ORDER by ID), more than rnn = ROW_NUMBER () (order by DisplayName), rndd = ROW_NUMBER () More than (ordercreate order by DESC), more than rnid = ROW_NUMBER (), (by ID DESC order), more than rnnd = ROW_NUMBER (), by order (DSAC), DBO. X1. *, DBO X2. * DOX1 Inner Joint DBX.x2 on DB.x1 Type = debob.x2 Type where username = @ username), X AS (selection Raunlmber = case @ Sortdayrekshn WHEN 'Aksh' then the case @ STB when 'Detstrakt So Ardblyuen' ID 'and when' Disple_anan Then RNN end 'Desc' case @sortBy When 'datecreate' then rndd when 'id' then rnid when 'display_name' then rnnd end, then select from DisplayName command as 'DisplayName'), from * x * RowNumber between RowNumber by @startRow and @endRow command;   
 But quite honestly I think you will get better performance from dynamic SQL (and if it's  optimization for ad-hoc workloads  setting enabled, then it affects Sniffing the underlying problems in the solution given above the parameter, or plan for cash bloat):  
  announcement @ sql NVARCHAR (max); SET @sql = N '; As with the order (select ROW_NUMBER () over (order by '+ @SortBy +' +SortDirection + '') RO number, debO.X1. *, Dbo.x2. * FROM dbo.x1 '; Inner dbo On the .x1.type = dbo.x2.type where the user name = @ user name) is selected as RowNumber, *, DisplayName DisplayName as the command from where the @startRow and @endRow command include RowNumber dbo.x2 between RowNumber; EXEC sp_executesql @sql, N 'usernem NVRAR (50), @trontoroint, andrew int', @usernamename, @startraw, endro;