Pass sorting parameter to Stores Procedure

I used Datagrid to show "Title", "Location" and "Date", It works very well.
I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date",
my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid.

Here is my stores procedure:

CREATE Procedure JobSearch
(
@.Search varchar(150),
@.Sort varchar(50)
)
AS

SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate

FROM
Job

WHERE
JobTitle LIKE '%' + @.Search + '%'
OR
JobKeywords LIKE '%' + @.Search + '%'

IF @.Sort = "Title"
ORDER BY JobTitle
IF @.Sort = "Location"
ORDER BY JobLocationState, JobLocationCity
IF @.Sort = "PostDate"
ORDER BY PostDate DESC

When I test stores procedure in SQL Server, I got the error about "Error 156: Incorrect syntax near the keyword 'ORDER' ".

Who has experience about stores procedure, please help me to correct this error.I am doing the same thing by working around using a string. Such as

Declare @.sql as varchar(2048)
...
exec(@.sql)|||Why you cannot use datagird to do the sorting?|||Very instersting, where's your code? In class or stores procedure? Could you show me more detail you code?

Thanks!|||I don't understand what you said. I just want to use datagrid to do the sorting.

The procedure as following:

1. Using the datagrid's AllowSorting property.
2. Writing a sortcommand event handler (retrieve e.SortExpression value, pass this value to component-class, class call SQL Server-stores procedure to retrieve new sort data and return to sortcommand event handler, and finally call bind function).

This is tree tiers program. If verything (include dataconnection and stores procedure) write in the same aspx page (two tiers program), it works.

I can write six different stores procedure in SQL Server, six classes in Component, then create a sub SortDataGrid function in aspx page to call these class and stores procedure, but this is not best way. I try to find the best way, one calss and one stores procedure in SQL Server. This stores procedure accept one parameter to do any kind of sorting.

What's your best way, could you show me?

Thanks!|||I think you'll need to use dynamic sql to achieve your desired effect. I've sketched it out below. Just make sure that the strings are closed/opened where appropriate. I don't have query analyzer available right now, so I'm sure there's a missing quote or an extra one somewhere below. But that's basically how you can do it.

CREATE Procedure JobSearch
(
@.Search varchar(150),
@.Sort varchar(50)
)
AS
DECLARE @.sql varchar(200)
SET @.sql =
'SELECT
JobTitle,
JobLocationCity,
JobLocationState,
PostDate
FROM Job
WHERE
JobTitle LIKE ''%'' + @.Search + ''%''
OR
JobKeywords LIKE ''%'' + @.Search + ''%'' '
IF @.Sort = "Title"
SET @.sql = @.sql + ' ORDER BY JobTitle'
IF @.Sort = "Location"
SET @.sql = @.sql + ' ORDER BY JobLocation, JobLocationCity'
IF @.Sort = "PostDate"
SET @.sql = @.sql + ' ORDER BY PostDate DESC'
exec(@.sql)

|||Here is how you can sort the datagird by usingDataView.|||You don't HAVE to use dynamic SQL. I'm pretty sure you can do this:

select ...
from ...
order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end|||I just checked this, and yes, it works. When I tried this last night at home on a laptop with no sql server installed, I used some website's online sql server interpreter to run a query similar to this, and it gave me an error, so I figured it was wrong. I just checked at work, and it works fine.|||The only problem with doing it this way is that Column1, Column2, etc either have to be the same data type or you need to CAST them to become the same data type.

select ...
from ...
order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end

You might find it better to use this method:

select ...
from ...
order by case when @.Sort = 1 then Column1 end, case when @.Sort = 2 then Column2 end

Terri|||Thanks Vito1281, Tmorton, and Pierre gave me about stores procedure suggestions. I will test these ways today and will report the result to you.

JimmyM suggest use Dataview, consider the two potential downsides:
1. there is the potential for the cached data to become stale.
2. Viewstate is maintained as a hidden HTML field, adding large objects to the viewstate can add several kilobytes, cause load the page very slowly.

So use viewstate isn't first choose in this case.

Thanks everyone !|||You may not quite understand what DataView is. DataView has nothing to do with caching data or Viewstate, it is a view (like the view you use at database) of a datatable in a dataset.
You can do something like filtering and sorting on the view and bind the view to your disply control. Here is one way of how to use it:

[Visual Basic]
Dim prodView As DataView = New DataView(prodDS.Tables("Products"), _
 "UnitsInStock <= ReorderLevel", _
 "SupplierID, ProductName", _
 DataViewRowState.CurrentRows)
[C#]
DataView prodView = new DataView(prodDS.Tables["Products"],
 "UnitsInStock <= ReorderLevel",
 "SupplierID, ProductName",
 DataViewRowState.CurrentRows);

|||JimmyM,

I am so sorry about it I made mistake to be DataView as ViewState. I never use Dataview in my program and I will learn about it. I will try to run the code follow your link.

I just test the stores procedure, which Vito1281 show to me, dynamic SQL. It work very well in SQL Server, and I will use this way first.

Amorton and Pierre have another suggestion (select ... from ...order by case when @.Sort = 1 then Column1 when @.Sort = 2 then Column2 end), I will test it later.

Thanks for everyone's help!