Is a cursor the best way to fetch a value? 4

[TroyMcClure]

Hi,
As you might guess, I'm relatively new to tsql, having only dabbled with pl/sql for Oracle in the past. Most of the programming I do uses ADO. So I'm wondering if the 'cursor' in tsql is--for most intents and purposes--the the counterpart to an ADO recordset.

For instance, let's say I have a stored procedure and all I want to do is get a single value from one field in a table. I pass the procedure the key value. Now, to simply fetch and return that value, is it mandatory that I open a cursor and fetch the row and then assign that fields value to the variable via the cursor?

I would like to do it in an easier, one-line way. I had tried the sytax:
Select CusName into @SomeVariable From tblCus Where CusID = @keyvalue

This doesn't work, but I might have the syntax wrong. Is some simple syntax like that available do do what I want? I know that something like that would of course be creating some cursor behind the scenes (in the same way dlookup() in MS Access creates a temp recordset), but from a standpoint of keeping the clutter down, am I on the right track in thinking it can be done this way?
Thanks,
james

 

[markVII]

Cursor in MS SQL sErver is a very expensive structure and should be used sparingly.
If you expect that your query returns exactly one row, you may use to get data.

declare @my_var VARCHAR(35)
select @my_var=CUST_NAME_S from CUSTOMER
PRINT @my_var

Now, if more than one row is returned, only the last value from the set will be assigned to the variable.
You could modify the query to return exactly one record:

declare @my_var VARCHAR(35)
select TOP 1 @my_var=CUST_NAME_S from CUSTOMER
PRINT @my_var

Note: this is not a behaviour to expect from other RDBMS. Oracle, for example, will throw an error when number of rows is more than one. And the cursors are completely different beast in Oracle...

 

[SQLSister]

Cursors should almost never be used in SQL Server, they are very expensive of resources. YOu need to start looking at things interms of returning or affecting a set of data, not looping through a recordset.

Try:
Select @SomeVariable = CusName From tblCus Where CusID = @keyvalue

This would populate the variable, but be careful with this construction, it will fail if they select returns more than one record.

To outpit this variable make it an output variable when you declare it or use a statment like this:

Select @SomeVariable

YOu cannot set the cvalue with a select and view it as a result set inthe same statement. Hope this helps.

 

[TroyMcClure]

Thank you both very much. I had no idea that cursors were not the preferred method. But how, then, would one structure a procedure that needs to loop--ie, a simple Update statement won't (or would be too complex) to process the sometimes byzantine logic that can be needed.

I guess that being raised on dao and ado may 'spoil' a developer because we get used to the flexibility those objects offered, but I just can't imagine doing all that I used to do in dao or ado via Update or Insert statements.

Would it be more efficient to do the looping in the client front end, say in a vb procedure or asp page, and call an sp for each update? I mean, it almost sounds like it'd be easier to do the looping via ado in an asp page or a vb module, and then call a stored procedure for each update. Is that way off base or is that a common method?
Thanks for all your expertise in this,
james

 

[markVII]

I did not mean - avoid cursors, just use them with caution.
For updates, use "forward only" cursor, as it consumes much less resources.

Using DAO/ADO (done my fair share there..)on the client would require additional round trips over the network; hardly superior solution.

 

[SQLSister]

You'd be surprised how complex you can get an update or insert. Looping through recordsets is a bad practice unless you have no other choice because you are doing something that can only be acted on one record at a time. Let me explain what happens when you loop. You call a recordset and move through it. Each time you reach a record that needs an Update, the update statement runs. Suppose you have a million records and 367,789 of them meet the criteria for an update. You would run 367,789 update statments. This process would take minutes and might even time out. Using a set-based update statment, the same process might take milliseconds.

A cursor won't hurt so badly if you don't have a large recordset although every little bit of inefficiency can hurt you as they all add up eventually. But since the thought process of looking at things in a set-based fashion is different than the procedural thought process, it is good to practice on the smaller recordsets too, so that you know exactly how to handle the process when you get to something large.

Further many programmers don't realize or can't predict in advance how large the production database will eventually be. It is much easier to learn once how to program correctly in a set-based fashion and create everything this way, than it is to try to fix a production database that has slowed to a crawl.

The Case statement is one way to allow a lot of flexibility in handling an update or insert. So is joining to another table and using the where clause to filter the records.

 

[markVII]

Set-based operations are always the way to go, and should be used whenever possible. The soul of SQL are set operations.

However, the procedural extensions (of which cursor is an impostant part) were not introduced just for fun of it. There are situations when only procedural approach will work.

 

[TroyMcClure]

sqlsister,
Yes, I understand about the Update statements being more efficient, but what I'm having a problem with is how to base a procedure on just those statments without any control loops. For instance, years ago I was involved with a banking system and I coded the nightly batch process.

This was done in VB with a db2 backend. Using RPG, I was able to loop through the tables containing the days transactions. Each transaction was handled differently, some needed to hit one set of gl tables, some another, some needed to join to numerous other ancilliary tables, and each one might branch differently based on any number of factors. And I'm only scratching the surface as to the complexity of what was done here.

Now, it may be lack of experience on my port, but I just can't imagine doing that with just a few (or even many) Update and Insert statements. I can't imagine doing that in any other way than with some sort of loop--true, I did the actual Updates and Inserts with SQL updates and not cursor updates--the cursors were used strictly to control and define the looping--but the procedure was controled by at a minimum one or two levels of nested loops.

The permutations of what different tables needed to be updated and what needed to be joined for each transaction type and situation was too great to code a bunch of Update statements, each filtered and formed differently--one transaction type, combined with that account holders specific situation that day (overdrawn, late payment, etc) might need to hit as many as 40 different tables, while another simpler type hits only a dozen. Please understand that I'm not trying to be argumentative--I very much would like to steer my programming methods to more efficient ways and I appreciate all your help, but I guess it's just getting past the 'looping' mentality that is where I'm stuck.
james

 

[SQLSister]

in this case I would have multiple statements to do the updates. Each one taking care of a particular case. Example, suppose I need to update table1 if the datefield in table2 is more thatn 30 days old. I also need to update table3 in this case. Table 4 needs to have records moved to an archive table if the Status in table2 is 'closed'. This process might need to happen once a week. I would write the following statements in a stored procedure and schedule it as a job.

update table1
Set field1 = 20
FROM table1, table2 
WHERE table1.IDField = table2.FKID
AND table2.dateofsomething < getdate()-30

update table3
Set field2 = 'overdue'
FROM table3, table2 
WHERE table3.IDField = table2.FKID
AND table2.dateofsomething < getdate()-30

insert table4archive (col1, col2, col3, col4)
Select table4.IDfield, table2.closedDate, 'closed', table4.col6
from table4 join table2
ON table4.IDField = table2.FKID
Where table2.status = 'closed'

Delete from table4 
FROM table4 INNER JOIN table4archive 
ON table4.IDField = table4archive.IDField

So there is no need to loop and everything is done as efficiently as possible. ANd actully there is less code to write. (In real life, I would add transaction processing to this.)

 

[markVII]

Just remember to do this within context of transaction, with proper safeguards.
Some thoughts:
And if you have to transform the data before committing an update, and the transformation is a notch above trivial? What if each row in the returned select must be treated differently? The CASE statement is fairly limited in what you can do with it, and user-defined function might not be an option( e.g. you need to manipulate NTEXT data type, which is not allowed in UDF signature), self-joins could be a nightmare to develop and maintain...
My point is that cursors have their place in database programming (even much maligned GOTO statement could be a most elegant solution!)

 

[SQLSister]

Agreed, there are cases where you have no other choice but to use a cursor. However, I submit that those cases are rare indeed and most cursor use can be avoided and should be avoided. Learning to think in a set-based fashion is an important skill for any developer who acceses databases.

 

[TroyMcClure]

Sqlsister,markvII
Thank you both very much. It's good to hear this because before tek-tips I would be in my own world doing things that, while they may may work, could be much improved upon.

In my previous few projects with Oracle I just learned enough about cursors that I would--on an almost line-for-line basis--susbsitute a cursor in place of the dao or ado recordset where I had either prototyped in access or ones I was just plain converting from Access. And I was only too happy with the huge performance increase over Access db's that I figured that was good enough. Well, it's too late to go back and retro fit those old apps but it's never too late to get on the right path. Thanks again,
--james