I write a SP that get 2 paramteres

one is the filed for order

and the second is the type of the ordered ("asc", or "desc")

here is my SP

CREATE PROCEDURE [dbo].[sp_PublicationPaging] (   
    @LanguageId int,
    @PageNum int,
    @TeamId int,
    @PracticeId int,
    @Ordered varchar(20),
    @AscDesc varchar(5),
    @totalCount        INT = NULL OUTPUT,
    @PageSize        INT = NULL OUTPUT
)

......

WITH Publication AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY @Ordered @AscDesc ) AS RowNum,
            b.PublicationId,

........

)

I got an error on the ordered syntax

 how can i sloved this problem?

thanks!

You need to convert your parameter as plain SQL satament first, then execute the statement

Declare @SQL Varchar(1000)

SET @SQL = ' SELECT ROW_NUMBER() OVER(ORDER BY ' + @Ordered + ', '  +  @AscDesc  + ') AS RowNum,
            b.PublicationId'

EXEC (@SQL)

 that no so simple because I have output parametere to return values with no success

 SET @SQL = '

WITH Teams AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY b.ShowOrder , c.header ,c.TeamId ) AS RowNum
         ...........
           
    )

'

EXEC (@SQL)

    SELECT  @totalCount = count(*)    FROM Teams;

 my question is how can i get the @totalCount from the results of the dynamic SQL

thanks

Why do you have to use OUTPUT? In this case, you will get the result from the query. Just use DataReader or DataAdapter to get the result from your query.

Here is a sample for CASE in Order by clause:

Declare @Ordered varchar(20)
Declare   @AscDesc varchar(5)
--Declare @mystring varchar(25)
SET @Ordered ='num1'
SET  @AscDesc='ASC'

;WITH Publication AS
    (
        SELECT col1, col2, col3, num1, ROW_NUMBER() OVER(
ORDER BY 

CASE @AscDesc
WHEN 'ASC' THEN
CASE @Ordered    
WHEN 'col1' THEN  col1 
  WHEN 'col2' THEN col2
  WHEN 'col3' THEN col3
 WHEN 'num1' THEN CONVERT(VARCHAR(50),num1)
END
END ASC,

CASE @AscDesc
WHEN 'DESC' THEN
CASE @Ordered 
 WHEN 'col1' THEN  col1
  WHEN 'col2' THEN col2
  WHEN 'col3' THEN col3
 WHEN 'num1' THEN CONVERT(VARCHAR(50),num1)
END 
END DESC

) AS RowNum, id
FROM [aTable]

)
select * from Publication

 GREAT!!!!

good Job!, its work wonderful

THANKS ALOT!

CASE @AscDesc
    WHEN 'ASC' THEN
        CASE @Ordered   
        WHEN 'Header' THEN  c.header
        WHEN 'Date' THEN  CONVERT(varchar,c.PublicationDate,112)
        END
END ASC,

CASE @AscDesc
WHEN 'DESC' THEN
CASE @Ordered
WHEN 'Header' THEN  c.header
     WHEN 'Date' THEN  CONVERT(varchar,c.PublicationDate,112)
END
END DESC

 Hi, I'm facing the same problem

Here my code.

1    ;with AgencyEntries As
2    	(
3    			select ROW_NUMBER() OVER (order by 
4    										case @ISortExpression
5    											when 'City' then a.City
6    											when 'SizeId' then s.SizeId
7    											when 'Network' then a.Network
8    											when 'CreationDate' then a.CreationDate
9    											when 'CompetencyLevel' then asr.CompetencyLevel
10   										end) as Row,
11   					a.AgencyId,a.Title,a.City,spec.Name,a.Network,
12   					s.SizeValue,convert(char(13),a.CreationDate,103)as CreationDate,s.SizeId,spec.SpecialityId,asr.CompetencyLevel
13   			from ACC_Agencies a
14   			inner join ACC_AgencySpecialityRel asr on asr.AgencyId = a.AgencyId
15   			inner join ACC_Specialities spec on spec.SpecialityId = asr.SpecialityId
16   			inner join ACC_Size s on s.SizeId = a.SizeId 
17   			where asr.CompetencyLevel != 0 
18   	)
19   
20   	select @ItemCount as TotalAgencies,AgencyId,Title,City,[Name],Network,SizeValue,CreationDate,SizeId,SpecialityId,CompetencyLevel 
21   	from AgencyEntries where Row between (@IPageIndex-1)*@IPageSize + 1 and @IPageIndex*@IPageSize

 It works with SizeId, CreationDate, CompetencyLevel but not with City or Network

Échec de la conversion d'une valeur datetime à partir d'une chaîne de caractères. ???

but

1    select ROW_NUMBER() OVER (order by a.City) as Row,
2            a.AgencyId,a.Title,a.City,spec.Name,a.Network,
3            s.SizeValue,a.CreationDate,s.SizeId,spec.SpecialityId,asr.CompetencyLevel
4    			
5    from ACC_Agencies a
6    inner join ACC_AgencySpecialityRel asr on asr.AgencyId = a.AgencyId
7    inner join ACC_Specialities spec on spec.SpecialityId = asr.SpecialityId
8    inner join ACC_Size s on s.SizeId = a.SizeId 
9    where asr.CompetencyLevel != 0

 is working...

can anyone have the answer for me ?