Find Highest Number Without Building an Index

[stanlyn]

Hi,

I need to find a way to get the row containing highest sessionid number WITHOUT building an index like I'm doing here. It takes too long to run and I'm trying to improve performance. Thanks! Here is the code...

Select 'rv_Session'
Index On SessionID Tag iSession
Set Order To iSession Desc
Go Top
Locate For Upper(Alltrim(rv_Session.SystemId)) == Upper(Alltrim(gcSystemId))

If Found('rv_Session')
    gnSessionId = rv_Session.SessionID + 1
Else
    gnSessionId = 1
Endif

 

[TamarGranor]

What is the difference with your use of ?m.lcSystemId and my use which is '<<lcSystemId>>' ?

Is one better than the other?

Looks like nobody answered this question. Yes, one is better. The ? notation protects you from SQL injection. That is, it keeps a hacker from getting access to your database:

https://www.w3schools.com/sql/sql_injection.asp

And here's the funny version of this:

https://xkcd.com/327/

Tamar

 

[Chris Miller]

Okay, coming back to this. I see I had a wrong understanding about the meaning of scope in SQL Server.

The same scope means the same function, procedure or the adhoc 'batch' of commands TABLEUPDATE creates. So even two successive SQLExecs are two separate scopes. Even when you uncheck Automatic Transactions in the view designer.

I showed you how you can circumvent this by defining a view the way it will either give you the one record of interest or none in your other thread. I'd go that route.

Chriss