Order by a Parameter

[joebickley]

Hi, im trying the following in a stored proc

Select * from tablex
order by coalesce(@sortfield, referencenumber)

I want it to sort by a field that i pass into it, if it is not provided i want to use the reference number but it doesnt work. I cant build up the SQL text and execute it as i have other stuff going on that makes that impracticle. If i remove the coalesce bit and just put the @sortfield again it doent work

Any ideas?

 

[hneal98]

You would have to do it dynamically like this:

declare @sql1 varchar(500)

select @sql1 =
'Select * from tablex
order by coalesce(' + @sortfield + ', referencenumber)'

exec(@sql1)

 

[SQLBill]

See Thread183-726596

-SQLBill

 

[jby1]

I have taken this code from a reply given to a similar query on this forum. I am not claiming credit for it though!!!

SELECT T.Field1, T.Field2, T.Field3
  FROM Table T
  WHERE [condition]
  ORDER BY 
    CASE WHEN @SortOrder = 'Field1' THEN T.Field1
         WHEN @SortOrder = 'Field2' THEN T.Field2
         ELSE T.ReferenceNumber END

 

[joebickley]

I know i can use the CASE method but with 50 fields its not very practicle.

And as for building the SQL string and executing it, like i said in the original post there are other things preventing that.

No matter i will look for another way

Thanks anyway