Passable ORDER BY value

[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

 

[Cor Ligthert[MVP]]

John,

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

Cor

 

[Aamir Mahmood]

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.

 

[John]

Hi

Mine is ms access database. Does not support @param I think.

Regards

 

[William Vaughn \(MVP\)]

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
____________________________________________________________________________________________

 

[Jon]

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.

 

[William Vaughn \(MVP\)]

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
____________________________________________________________________________________________