Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 9th, 2004, 10:15 AM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default cursor parameters???

Hi all, I'm new to the p2p community.

I come from an Oracle pl/sql background and I now am working with SQL Server/Transact-SQL. I'm slowly but surely learning how to do things in T-SQL that I was doing in Oracle, but I'm running into a dead end on this one. Cursor parameters.

Apologies to those who know exactly what I'm talking about, but for those who don't let me explain. In Oracle, cursor parameters are simply that. They're parameters passed to a cursor (which as been declared as such) to alter the cursor query dependent upon the parameter passed. For example, say you have a table called "usa_city_states" which contain at least two columns called "city" and "state listing all the cities in each state in the US. Now say you need to use the information from that table to then do a select from another table which contains tax mill rate info based on city and state. The issue is that you only want to work with the data one state at a time.

The idea is that you could create a cursor with a cursor variable (which would be the state in the aforementioned example). And when you open the cursor, you specify the value of the cursor variable/parameter which is then used in the cursor query to narrow the scope of the cursor. So in **psuedocode** (which I'll try to keep close to t-sql syntax) it would look something like:

CREATE PROCEDURE my_proc
     @state_i_care_about CHAR(2) = NULL
AS
DECLARE
        @city_var VARCHAR(100),
        @state_var CHAR(2)

DECLARE my_cursor(@cursor_parm CHAR) CURSOR
FOR
   SELECT city, state
   FROM usa_city_states
   WHERE state = @cursor_parm
FOR READ ONLY

/* main block */

OPEN my_cursor(@state_i_care_about)
FETCH NEXT FROM my_cursor INTO @city_var, @state_var
WHILE @@FETCH_STATUS != -1
BEGIN
  SELECT tax_rate
  FROM tax_table
  WHERE tax_city = @city_var
  AND tax_state = @state_var
END
CLOSE my_cursor
DEALLOCATE my_cursor

/* end main block */

Now maybe I'm just not seeing it, but I've looked at 3 books and have gone out to MSDN and I can't find where T-SQL let's you do anything like this and I just can't believe it because it's **core** functionality in Oracle pl/sql programming!

If there is such a way to do this in T-SQL for SQL Server 7.0 could someone please post? THANKS!
Reply With Quote
  #2 (permalink)  
Old July 9th, 2004, 03:22 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Normally, I would just try to provide the answer to the question, but I have to ask about what's going on here. It seems like using a cursor is a terribly klunky way of do this. Why are you not using a joined select?

Actually, now that I look at the SQL again, why not use a single query on the tax_table table where you specify the state? This seems way more complicated than it needs to be.
Reply With Quote
  #3 (permalink)  
Old July 9th, 2004, 04:19 PM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply. The example I gave was just to demonstrate the use of cursor parameter. It's not the project I'm working on, so don't analyze its "klunkiness." It's nothing more than an example. (Apologies if I didn't make that clear enough in my original post.)

The benefit of a cursor parameter is that it makes a cursor more *reusable*. Instead of hardcoding values into the where clause, you can substitute the value passed by the parameter. Pretty useful if you ask me. Also, feel free to ask any Oracle programmer.

In my project (which is fairly involved and is not necessary for just asking about this functionality) I want to use a cursor where the scope of the select needs to change but the tables joined and columns to be returned and are always the same. So a cursor with parameters would be a good solution and I'm only trying to find out if anyone knows of a similar functionality in MS transact-SQL.

Thanks again.
Reply With Quote
  #4 (permalink)  
Old July 9th, 2004, 10:58 PM
Authorized User
 
Join Date: Jul 2004
Location: Aurangabad, MAharashtra, India.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jhawar
Default

Hi,
   I faced the same problem and I also tried no. of books and sites...but passing parameters to Cursors was nowhere a topic.
I came out with an easy solution....instead of passing parameter to a cursor what u can do is..incorporate that cursor in a stored procedure and pass the parameter to the stored procedure.
I have earlier given a solution on this...

http://p2p.wrox.com/topic.asp?TOPIC_ID=15728

Go through this reply in which i have created two stroed procedures...

Hope this helps.



Amit Jhawar
Developer
Reply With Quote
  #5 (permalink)  
Old July 11th, 2004, 05:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi evaldesc,

First of all, I would like to know why you have such a love for CURSOR there? When one would suggest you always to avoid cursor to the maximum possible. And in your case I don't really see any need for a CURSOR there, though it is just an example. What made you go for CURSOR as a solution to this. May be you can explain the exact scenario and then can expect suggestions from here on whether CURSOR would help you or some other ways of going about.

But IMO, I would say CURSOR is not the right solution to the example that you posted here, as Planoie suggested a join would do that for you so easily. I would also suggest you to explore on what effects would a cursor have on the SQL server.

Quote:
quote:So a cursor with parameters would be a good solution
FYI, CURSOR doesn't take PARAMETERs in T-SQL.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #6 (permalink)  
Old July 12th, 2004, 08:50 AM
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

To jhawar - thanks for the tip. :)

To happygv,

Sorry, but if you feel that cursor usage doesn't have it's place in SQL programming, I couldn't disagree with you more. Just like ANY tool, I believe there are almost always situations where one tool excels over another. I feel cursors are very advantageous when migrating data from one database to another -- especially because of the ability to invoke procedural logic on row-by-row transactions versus trying to encompass one SELECT statement that works for every row being converted. When migrating data, there could be several mapping rules due to the fact that the dbs are not similar. This is usually the case because vendors create tables differently, or their normalized structure may be based on a different focus, or just the fact that newer databases are sometimes further normalized than older ones.

When migrating data in row-by-row fashion, you can retrieve the data from table rows in the old database and then perform (easy to read) procedural logic on various columns where necessary before INSERTing them into the new table(s). I believe this is were cursors shine. And if you can nest cursors and pass parameters to the nested cursor, then you get something very powerful because you can cycle through a more normalized table (refered to by the nested cursor) that may have several rows for each parameter key passed, thus allowing you to perform procedural logic in the outer cursor loop that is relevent to the group, and seperate procedure logic in the inner cursor that is relevant to the row.

Additionally, what if, depending upon the row data you may need to parse out data into mulitple tables because the data has been normalized further in the new database? If you try to do everything in a INSERT AS SELECT statement, you find yourself needing nested case statements which could go down several layers just to replicate the same logic that could be more easily done in procedural code. And you'd be writing the same SELECT statement more than once to accomplish each insert when you could simply reuse a cursor and simply change one field?

I'm sorry, but this is functionality that has existed in Oracle for several versions (at least back to 7) and even Oracle pl/sql pundits such as Steven Feuerstein do not negate their usefulness in certain situations, so please forgive my skepticism regarding your comments.
However, I appreciate them nonetheless.

Now that I know that cursor parameters don't exist in transact-SQL, I will stop pining for them. Thanks again.

Regards
Reply With Quote
  #7 (permalink)  
Old July 12th, 2004, 09:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi evaldesc,

I agree with all that you say on how advantageous a cursor can be in a SUITABLE situation where it CAN BE APPLIED. Maybe, if you had given your exact situation where you would have liked to use the cursor(other than the sample you mentioned to which CURSOR is not the only solution), I wouldn't have suggested you not to go for it. IMO, neither your example needed a CURSOR as its only solution nor your explanation wanted a cursor. It should be used in cases where it is REALLY applicable and cannot be substituted with any other. And if you have time please read my previous post again, which says your example is not the one for which a CURSOR is really suitable. I am not totally against using cursor, knowing how beneficial it is in its own situation.

There are places where only cursors can fit into and do the job. I just suggested to avoid cursor to the maximum possible not totally, as it has performance hit on the SQL server, for which you can go through some articles dealing with the performance issues.

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #8 (permalink)  
Old July 26th, 2007, 04:16 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This worked for me:

declare @deptId numeric(10,0), @deptName varchar(100), @empName varchar(10)

set @location = 'NEW YORK'

declare cur1 cursor for
select dept_id, dept_name
from departments
where location = @location

open cur1
fetch next from cur1 into @deptId, @deptName
while (@@FETCH_STATUS=0) begin

    declare cur2 cursor for
    select emp_name
    from employees
    where dept_id = @deptId

    set @empName= ''

    open cur2
    fetch next from cur2 into @empName

        while (@@FETCH_STATUS=0) begin
        print @empName
        fetch next from cur2 into @empName
        end
    close cur2
    deallocate cur2
    fetch next from cur1 into @deptId, @deptName
end
close cur1
deallocate cur1
Reply With Quote
  #9 (permalink)  
Old July 28th, 2007, 02:53 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Good luck with that.

--Jeff Moden
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
Regarding Cursor param99 SQL Server 2000 1 September 8th, 2006 10:03 AM
Regarding Cursor param99 SQL Language 0 September 8th, 2006 03:56 AM
cursor trinnie SQL Server 2000 0 August 6th, 2006 09:55 PM
Cursor Question kilika SQL Server 2000 8 August 21st, 2003 01:34 PM



All times are GMT -4. The time now is 03:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.