Coding Advice

[AndrewdAzotus]

For those that have far greater experience with VB and SQL Server than I, I wondered if this is bad:

Dim SQLCmd As New SqlCommand("Select Descr from yModList Where Idx = " & New SqlCommand("Select Max(Idx) from yModList", Cxn).ExecuteScalar, Cxn)

and (more importantly) Why?

I have a table called yModList with two columns: Idx (int and pri-key) and Descr (string)

the idea of this SQLCmd is to extract the Descr from the row where Idx is the highest value. It seems fairly obvious to me but I wondered if there would be any hidden issues...

 

[jmcilhinney]

It's not good. Regardless of anything else, I would execute the first command separately rather than combining it all into one line. That wouldn't stop it working though. More importantly, there's no need for two commands. I not sure but you may just be able to incorporate the inner query into the main query, but even that's not necessary. You simply query the table and order by Idx in descending order and then take the TOP 1. It's just one relatively simple query.

 

[AndrewdAzotus]

Thank you for that. However, I know from my own coding experience (outside of SQL) that sorts can be hungry, so I investigated a little more and have decided on this:

Dim SQLCmd As New SqlCommand("Select Descr from yModList Where Idx = (Select Max(Idx) from yModList)", Cxn)

Since Idx is the primary key, it is unique and indexed so max(idx) should be quick and easy to find and, logically, it should be the most efficient.

(Must re-learn my SQL)