Passable ORDER BY value

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

In my TableAdapter I am using a parameterised sql as below;

SELECT <field list>
FROM Clients
WHERE (ID = ?)

My question is, is it possible to also pass the value for ORDER BY too as a
parameter, so I can have something like;

SELECT <field list>
FROM Clients
WHERE (ID = ?)
ORDER BY ?

Thanks

Regards
 
John,

AFAIK this is impossible, however why do you not set simple the order in the
DefaultView of the received DataTable?

Cor
 
You can try something like this:

SELECT <field list>
FROM Clients
WHERE (ID = ?)
ORDER BY
case @OrderBy
when 'Feild1' then Feild1
when 'Feild2' then Field2
when 'Feild3' then Field3
...
end

Where @OrderBy is the passed parameter.
 
Sure you can. Just go into the TableAdapter GUI designer and click on the
Fill. Choose Configure and add in the Order By clause.

However, I don't (generally) use ORDER BY in my SQL as I use bound controls
that have their own sort functionality or I use a DataView that also can
sort the data client-side. It makes the query run faster as it moves
unnecessary work off of the server.


hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
Slightly off topic...

If a lot of rows could potentially be read back and you make your select statement restrict the
number of rows that you want read back, I assume it would be better then to get the server to do the
order by?


Sure you can. Just go into the TableAdapter GUI designer and click on the
Fill. Choose Configure and add in the Order By clause.

However, I don't (generally) use ORDER BY in my SQL as I use bound controls
that have their own sort functionality or I use a DataView that also can
sort the data client-side. It makes the query run faster as it moves
unnecessary work off of the server.
 
I doubt it. Look at the query plan. If the sort is done high in the process
tree against a large rowset it might be cheaper to send the job to the
client.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
Back
Top