cursor learning - 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

General SQL Server Forums

New to SQL Server Programming

cursor learning

Author

Topic

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 08:51:50

Hello, I am trying to cycle through a table using cursor to fetch id and date of birth. It is a real small table and I am trying to understand cursors.

At the end I am trying to insert the data into a table and the code runs but the table is empty? help....

DECLARE @pt_pid1 numeric(19,0),
@pt_dob1 datetime

-- declare the cursor

DECLARE diabetic_pt1 CURSOR FOR
select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

OPEN diabetic_pt1
FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1
-- start the main processing loop.

WHILE @@Fetch_Status = 0

BEGIN

-- This is where you perform your detailed row-by-row
-- processing.
-- Get the next row.

FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1
END

CLOSE diabetic_pt1

DEALLOCATE diabetic_pt1

RETURN

insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1)
values (@pt_pid1,@pt_dob1);

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 08:55:14

Move the insert statement into the loop (after the begin statement)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 08:55:23

insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1)
values (@pt_pid1,@pt_dob1);

should be with Begin and End

But this can be easily done with just this without cursor
insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1)
select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 08:55:42

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-28 : 08:57:04

quote:
Originally posted by barflyz

Hello, I am trying to cycle through a table using cursor to fetch id and date of birth. It is a real small table and I am trying to understand cursors.

At the end I am trying to insert the data into a table and the code runs but the table is empty? help....

DECLARE @pt_pid1 numeric(19,0),
@pt_dob1 datetime

-- declare the cursor

DECLARE diabetic_pt1 CURSOR FOR
select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

OPEN diabetic_pt1
FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1
-- start the main processing loop.

WHILE @@Fetch_Status = 0

BEGIN

-- This is where you perform your detailed row-by-row
-- processing.
insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1)
values (@pt_pid1,@pt_dob1);
-- Get the next row.

FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1
END

CLOSE diabetic_pt1

DEALLOCATE diabetic_pt1

RETURN

No, you're never too old to Yak'n'Roll if you're too young to die.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 09:26:24

if I wanted to insert 2 more columns by joining on id in the first cursor why do I get this error?

Error: Must declare the scalar variable "@pt_pid1". (State:37000, Native Code: 89)
Error: Incorrect syntax near 'varchar'. (State:37000, Native Code: 66)
Error: Incorrect syntax near 'varchar'. (State:37000, Native Code: 66)

--a1c values--
DECLARE @a1c_value1 varchar(254),
@a1c_value2 varchar(254)
-- declare the cursor

DECLARE get_a1c_values CURSOR FOR
select rptobs.obsvalue, rptobs.obsdate
from rptobs
where rptobs.hdid = 28
and rptobs.pid = @pt_pid1 (from previous posted cursor example)
order by rptobs.obsdate desc;

OPEN get_a1c_values
FETCH get_a1c_values INTO @a1c_value1 varchar(254)
FETCH get_a1c_values INTO @a1c_value2 varchar(254)

-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN

-- This is where you perform your detailed row-by-row
-- processing.
-- Get the next row.

insert into bds_diabetic_values_tmg (a1c_value1,a1c_value2)
values (@a1c_value1,@a1c_value2);

FETCH get_a1c_values INTO @a1c_value1,
@a1c_value2
END
CLOSE get_a1c_values
--DEALLOCATE get_a1c_values
RETURN

thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 09:46:27

Error: Must declare the scalar variable "@pt_pid1". (State:37000, Native Code: 89)

You haven't declared that variable in this script.

Error: Incorrect syntax near 'varchar'. (State:37000, Native Code: 66)

You don't need to put the datatype (varchar) in the fetch statement.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 09:55:31

hI DBA in the making

It is in the first example for id, can I not run these one at a time if it is calling out a first cursor?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 10:15:05

quote:
Originally posted by barflyz

hI DBA in the making

It is in the first example for id, can I not run these one at a time if it is calling out a first cursor?

Variables go out of scope once execution stops. By the time your first query finishes execution, the variable @pt_pid1 is destroyed, and any value it contained is lost.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 10:18:14

How about if I combine like this? Bit I still get an error named - Error: Incorrect syntax near the keyword 'FETCH'. (State:37000, Native Code: 9C) and having trouble locating it

DECLARE @pt_pid1 numeric(19,0),
@pt_dob1 datetime,
@a1c_value1 varchar(254),
@a1c_value2 varchar(254)
-- declare the cursor

DECLARE diabetic_pt1 CURSOR FOR
select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

DECLARE get_a1c_values CURSOR FOR
select rptobs.obsvalue
from rptobs
where rptobs.hdid = 28
and rptobs.pid = @pt_pid1
order by rptobs.obsdate desc;

OPEN diabetic_pt1
FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1;

OPEN get_a1c_values
FETCH get_a1c_values INTO @a1c_value1
FETCH get_a1c_values INTO @a1c_value2
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN

-- This is where you perform your detailed row-by-row
-- processing.
-- Get the next row.

insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1,a1c_value1,a1c_value2)
values (@pt_pid1,@pt_dob1,@a1c_value1,@a1c_value2)

[I think the error os around this part]

FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1

FETCH get_a1c_values INTO @a1c_value1,
FETCH get_a1c_values INTO @a1c_value2

END
CLOSE diabetic_pt1
CLOSE get_a1c_values

DEALLOCATE diabetic_pt1
DEALLOCATE get_a1c_values

RETURN

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 10:22:28

The "," at the end of the first line here is illegal:

FETCH get_a1c_values INTO @a1c_value1,
FETCH get_a1c_values INTO @a1c_value2

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 10:24:38

I just found that but the data does not insert into the table? I am learning.... thanks

DECLARE @pt_pid1 numeric(19,0),
@pt_dob1 datetime,
@a1c_value1 varchar(254),
@a1c_value2 varchar(254)
-- declare the cursor

DECLARE diabetic_pt1 CURSOR FOR
select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

DECLARE get_a1c_values CURSOR FOR
select rptobs.obsvalue
from rptobs
where rptobs.hdid = 28
and rptobs.pid = @pt_pid1
order by rptobs.obsdate desc;

OPEN diabetic_pt1
FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1;

OPEN get_a1c_values
FETCH get_a1c_values INTO @a1c_value1
FETCH get_a1c_values INTO @a1c_value2
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN

-- This is where you perform your detailed row-by-row
-- processing.
-- Get the next row.

insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1,a1c_value1,a1c_value2)
values (@pt_pid1,@pt_dob1,@a1c_value1,@a1c_value2)

FETCH diabetic_pt1 INTO @pt_pid1,
@pt_dob1

FETCH get_a1c_values INTO @a1c_value1
FETCH get_a1c_values INTO @a1c_value2

END
CLOSE diabetic_pt1
CLOSE get_a1c_values

DEALLOCATE diabetic_pt1
DEALLOCATE get_a1c_values

RETURN

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 10:27:26

I get this message

This command did not return data, and it did not return any rows

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 10:32:52

Try popping a few PRINT statements into your code to see what it's doing.

Also, the outer cursor (diabetic_pt1) doesn't have a WHILE loop, so that will only execute once, for the first values returned by the fetch

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 10:36:34

I wish I understand but I don't.... can you give me an example in my code of how the while would fit in?

thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 10:44:41

quote:
Originally posted by barflyz

I wish I understand but I don't.... can you give me an example in my code of how the while would fit in?

thanks

Sorry, ignore that bit about the while loop. I just realised you were fetching from both cursors inside the while loop. What gets returned when you run the following 2 select queries?

select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

select rptobs.obsvalue
from rptobs
where rptobs.hdid = 28
and rptobs.pid = @pt_pid1
order by rptobs.obsdate desc;

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 10:51:28

1,946 ids and dobs from qry 1
and thousands of test results for the same pids if the join to = @pt_pid1 worked

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 10:52:20

I am trying to insert last most recent test to each pid and then 2nd most recent test in another col for each pid

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 11:13:09

If you put the following before the insert line, does it print anything out into the messages tab?

PRINT @pt_pid1
PRINT @pt_dob1
PRINT @a1c_value1
PRINT @a1c_value2

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 11:23:03

I still get This command did not return data, and it did not return any rows and it does not print anything?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 12:20:08

Try this:

DECLARE @pt_pid1 numeric(19,0),
@pt_dob1 datetime,
@a1c_value1 varchar(254),
@a1c_value2 varchar(254)
-- declare the cursor

DECLARE diabetic_pt1 CURSOR FOR
select diabeticpatients.id,
diabeticpatients.dob
from diabeticpatients;

OPEN diabetic_pt1
FETCH diabetic_pt1 INTO @pt_pid1, @pt_dob1;
WHILE @@Fetch_Status = 0
BEGIN
	DECLARE get_a1c_values CURSOR FOR
	select rptobs.obsvalue
	from rptobs
	where rptobs.hdid = 28
	and rptobs.pid = @pt_pid1
	order by rptobs.obsdate desc;

	OPEN get_a1c_values
	FETCH get_a1c_values INTO @a1c_value1
	FETCH get_a1c_values INTO @a1c_value2
	-- start the main processing loop.
	WHILE @@Fetch_Status = 0
	BEGIN
	-- This is where you perform your detailed row-by-row
	-- processing.
	-- Get the next row.

		insert into bds_diabetic_values_tmg (pt_pid1,pt_dob1,a1c_value1,a1c_value2)
		values (@pt_pid1,@pt_dob1,@a1c_value1,@a1c_value2)


		FETCH get_a1c_values INTO @a1c_value1
		FETCH get_a1c_values INTO @a1c_value2
	END 

	CLOSE get_a1c_values
	DEALLOCATE get_a1c_values

	FETCH diabetic_pt1 INTO @pt_pid1,
	@pt_dob1

END

CLOSE diabetic_pt1
DEALLOCATE diabetic_pt1

RETURN

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources