Stored Procedure Using A Declared Cursor

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

Stored Procedure Using A Declared Cursor

Author

Topic

Sapphire
Starting Member

4 Posts

Posted - 2007-11-15 : 21:17:14

I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.

The doctors table has the following columns with specialism allowing NULL values

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

Any help would be greatly appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 21:18:53

You really don't need to cursor / loop through records do do this type of thing.

It is impossible to write what you ask for, or a possible replacement without some sample data and sample results for that data that would go in the table you describe.

Poor planning on your part does not constitute an emergency on my part.

Sapphire
Starting Member

4 Posts

Posted - 2007-11-15 : 21:35:38

A sample of the data being inserted in to the doctors table

INSERT INTO doctor VALUES('1','John Doe','Doctor',NULL)
INSERT INTO doctor VALUES('2','Fred Bloggs','Registrar','Dermatology')

Its being annoying me and never realised how incomplete the question was. Sorry for the Inconvenience.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-11-16 : 04:11:39

CREATE PROCEDURE DoctorsWithSpecialisms
AS
BEGIN
SELECT staff_no,doctor_name,specialism FROM doctor WHERE specialism IS NOT NULL
END

You can apply different formatting to the recordset. I prefer to apply formatting at the client application

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

ericajones20
Starting Member

4 Posts

Posted - 2007-11-29 : 15:39:12

A second question also posted on this same forum, I am priviledged and famous (or infamous)

Eric

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-29 : 23:27:47

Eric, so Sapphire is one of your students ?

KH
[spoiler]Time is always against us[/spoiler]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-30 : 07:56:23

Eric: why teach your students to use cursors? that's like teaching them to use bubblesort.

elsasoft.org

ericajones20
Starting Member

4 Posts

Posted - 2007-12-12 : 08:56:54

Yep but strangely hasnt replied to my message :D

quote:
Originally posted by khtan

Eric, so Sapphire is one of your students ?

KH
[spoiler]Time is always against us[/spoiler]

ericajones20
Starting Member

4 Posts

Posted - 2007-12-12 : 08:57:52

DUH...Maybe because its on the Syllabus?

quote:
Originally posted by jezemine

Eric: why teach your students to use cursors? that's like teaching them to use bubblesort.

elsasoft.org

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-12-12 : 09:00:29

You teach for a backwater, non accredited institution I take it?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 09:03:31

quote:
Originally posted by ericajones20

DUH...Maybe because its on the Syllabus?

quote:
Originally posted by jezemine

Eric: why teach your students to use cursors? that's like teaching them to use bubblesort.

elsasoft.org

What, you teach your students the wrong way to program in SQL simply because it is on the sillybus? If you maybe taught them to do the same thing in a set based way, would they fail the course?!?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-12 : 09:19:48

quote:
Originally posted by ericajones20

DUH...Maybe because its on the Syllabus?

quote:
Originally posted by jezemine

Eric: why teach your students to use cursors? that's like teaching them to use bubblesort.

elsasoft.org

wow, that's great. now I really feel like I missed out, not taking any CS in college.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources