I am kind of posting frantically here and moving on before getting
replies... hehe.. I'm on a roll! I do see the simplicity of adding the
ASC/DESC into the EXECUTE sp_ExecuteSql method. I did not ask my question
clearly... I was looking for a way other than the sp_ExecuteSql method. I
had found further information in the meantime that suggested using EXEC
or
EXECUTE sp_ExecuteSql has many disadvantages. I am now playing with this
setup:
The disadvantages that you refer to are that anytime you EXEC @sql or EXEC
ExecuteSql @sql, you are forcing SQL Server to parse, optimize and compile
your code, whereas a plain vanilla stored procedure that's not dynamic
only gets parsed optimized and compiled when it's created; not every time
it's run.
If you do a lot of dynamic stuff like this, then here are your options:
1. Build your SQL like this:
"EXECUTE sp_DexecuteSQL " & sql
and assign it to a SqlCommand.CommandText
2. Build your SQL in a string and pass it to a SqlParameter.Value wich you
Add() to a SqlCommand.Parameters collection where the
SqlCommand.CommandText is "sp_ExecuteSQL"
One thing you will want to consider is the subtle differences between
these:
"EXECUTE @sql" and "EXECUTE sp_ExecuteSql @sql"
I personally am a bit weak on the details, but if you bump over to the
microsoft.public.sqlserver.programming group, you can ask them the
differences, and they are THE final SQL gods.
SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
FROM tblMain
ORDER BY
CASE @Asc_Desc
WHEN 'desc' THEN
CASE @SortBy
WHEN 'MainID' THEN MainID
END
END
Desc,
You *definately* want to avoid this foolishness. Any DBA would slap you
with your own mouse cable
The last part of my
task is how/where in my asp.net code do I communicate the asc or desc
parameter? I need to put something in place so that the page knows
whether
its the first or second click etc... so it can pass the correct asc/desc
value. I'm not sure where/how that code will go.
First, assume ASC by default. It's easyier that way. Then you put a
checkbox on your WebForm:
[x] Descending
You set the checkbox to NOT do a PostBack.
Then you make your DataGrid or whatever you're using that the user clicks
to actually perform the sort do the PostBack. Then (I live in COM+
Enterprise Services and I'm weak on asp) you do something like this in
your CodeBehind Class (aircode):
[VB]
Private Sub PageLoad(stuff)
Dim sql As String = "your stuff"
If Page.IsPostBack() Then
If chkDescending.Checked Then sql &= " DESC"
End If
Dim sortBy = sender.Name '?
Dim cm As New SqlCommand(sql, conn)
With cm ' WAY faster
.CommandText = sql
With .Parameters 'WAY faster
.Add("@sortBy", SqlDbType.VarChar)
.Item(0).Value = sortBy
etc.
[CS]
private void pageLoad(stuff) {
string sql = "your stuff";
if (Page.IsPostBack)
if (chkDescending.Checked) sql += " DESC";
string sortBy = sender.Name;
SqlCommand cm = new SqlCommand(sql, conn);
cm.CommandText = sql;
cm.Parameters.Add("@sortBy", SqlDbType.VarChar);
cm.Parameters(0).Value = sortBy
etc.
--
Peace & happy computing,
Mike Labosh, MCSD
"I have no choice but to believe in free will."
