keeping the cursor alive! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

keeping the cursor alive!

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-02-03 : 08:38:24

Hey all,

I am trying to create a stored procedure that returns a new recordset everytime I execute it. I have a table (A$) from which I want to grab records from column A(ticker). Each time I execute the sproc, I want to see the next value in column A. I have tried this using a cursor. The follow code returns all values found in column A instead of each value individually. How can I keep the cursor alive so that each new execute returns the next value. Can I use a global cursor or something?? Many thanks.
///////
CREATE PROCEDURE ssTEST

AS

DECLARE @ticker VARCHAR(128)

DECLARE blp CURSOR FOR
SELECT DISTINCT ticker
FROM A$

OPEN blp

FETCH NEXT FROM blp
INTO @ticker

WHILE @@FETCH_STATUS = 0

BEGIN

SET NOCOUNT ON

CREATE TABLE #RES
(
TICKER VARCHAR(128)
)

INSERT INTO #RES (TICKER)
SELECT @TICKER

SELECT TICKER
FROM #res

FETCH NEXT FROM blp
INTO @TICKER

DROP TABLE #RES

END

CLOSE blp
DEALLOCATE blp
GO
////////

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 08:44:22

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 08:45:42

>> How can I keep the cursor alive so that each new execute returns the next value.
You can't
>> Can I use a global cursor
no
>> or something??
yes - see my signature
>> Many thanks.
You're welcome

You need to persist the data to keep track of the previous call. This either means saving the state or returning it with each call.
You then have a choice of recreating the recordset with each call or persisting it wit hthe first call - the choiuce may depend on the volatility of the data and the resources needed to create the set.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-03 : 08:51:54

[code]use Northwind
create table #data
(
ticker varchar(128)
)

insert into #data select FirstName from Employees
Result : Andrew, Anne, Janet, Laura, Margaret, Michael, Nancy, Robert, Steven

CREATE PROCEDURE ssTEST
@ticker varchar(128)
as
begin
select top 1 ticker
from #data
where @ticker is null
or ticker > @ticker
order by ticker
end

exec ssTEST NULL
Result : Andrew
exec ssTEST 'Andrew'
Result : Anne
exec ssTEST 'Anne'
Result : Janet[/code]

----------------------------------
'KH'

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-02-03 : 09:08:01

Table:
columnA
a
b
c
d

result after first execute = a
result after second execute = b
result after third execute = c

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-02-03 : 09:08:40

And I don't have the option of using input parameters......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-03 : 09:11:36

quote:
Originally posted by dirwin26

And I don't have the option of using input parameters......

any particular reason can't ?

----------------------------------
'KH'

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-02-03 : 09:15:34

I am automating the input.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 09:21:12

Why do you want to do this?
Do you want to create alphanumeric primary key?

Madhivanan

Failing to plan is Planning to fail

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-02-03 : 09:26:37

nope, i want the user to see a new result each time they click the submit button on the web.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 09:32:52

How do you define the new Result?
Do you mean randomly select a record from table?

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-03 : 09:35:26

Is the stored procedure aware of who is calling it? In other words with many concurrent users each calling the sp 1 time do you still need a different result for each call? And what happens when all the values have been returned 1 time, should it start over at the beginning?

How about a random 1 row?


select top 1 ticker from a$ order by newid()

Be One with the Optimizer
TG

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-02-03 : 09:59:50

Random is exactly what I need. It works perfectly.

Thanks for all the suggestions. Much appreciated.

Ciao

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 10:08:13

And there is no need of using cursor. You didnt specify the random selection in the question itself. Otherwise you might have got solution quickly

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-03 : 10:53:31

If we only the knew the right questions to begin with we probably wouldn't need any answers.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources