cursors help-pubs database - 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

cursors help-pubs database

Author

Topic

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-20 : 11:41:17

This is how i List the titles and author names, in general sql, but i have no idea using cursor. So, can anyone help me to create a cursor that loops through the authors table.
select
au_lname,
au_fname,
title
from
authors a
join titleauthor ta on (a.au_id=ta.au_id)
join titles t on (ta.title_id=t.title_id)

below is the ddl for the three tables.

CREATE TABLE [dbo].[titleauthor](
[au_id] [dbo].[id] NOT NULL,
[title_id] [dbo].[tid] NOT NULL,
[au_ord] [tinyint] NULL,
[royaltyper] [int] NULL,
CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED
(
[au_id] ASC,
[title_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[titleauthor] WITH CHECK ADD FOREIGN KEY([au_id])
REFERENCES [dbo].[authors] ([au_id])
ALTER TABLE [dbo].[titleauthor] WITH CHECK ADD FOREIGN KEY([title_id])
REFERENCES [dbo].[titles] ([title_id])

CREATE TABLE [dbo].[titles](
[title_id] [dbo].[tid] NOT NULL,
[title] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[type] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('UNDECIDED'),
[pub_id] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NULL,
[advance] [money] NULL,
[royalty] [int] NULL,
[ytd_sales] [int] NULL,
[notes] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pubdate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED
(
[title_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[titles] WITH CHECK ADD FOREIGN KEY([pub_id])
REFERENCES [dbo].[publishers] ([pub_id])

CREATE TABLE [dbo].[authors](
[au_id] [dbo].[id] NOT NULL,
[au_lname] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[au_fname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[phone] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('UNKNOWN'),
[address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contract] [bit] NOT NULL,
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED
(
[au_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[authors] WITH CHECK ADD CHECK (([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'))
GO
ALTER TABLE [dbo].[authors] WITH CHECK ADD CHECK (([zip] like '[0-9][0-9][0-9][0-9][0-9]'))

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-20 : 12:56:28

quote:
This is how i List the titles and author names, in general sql, but i have no idea using cursor. So, can anyone help me to create a cursor that loops through the authors table.

Um, why do you need to loop through?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-21 : 22:21:57

This is how i created the cursor and suggestions would be helpful. Also, i'm using the pubs database. Thank you anyone for going through the ddl and my cursor script.

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)

PRINT '-------- Authors and Titles report --------'

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Books by Author and their titles: ' +
@au_fname + ' ' + @au_lname

PRINT @message

-- Declare an inner cursor based
-- on au_id from the outer cursor.

DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor

OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title

IF @@FETCH_STATUS <> 0
PRINT ' <<No Books>>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = ' ' + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title

END

CLOSE titles_cursor
DEALLOCATE titles_cursor

-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-21 : 22:50:53

what are you trying to achieve? Generally if you know how to get what you want with a set based method, use that. In your first post you have a set based method right at the top. so why mess with cursors? they will just make your app run slower.

if just you want your app to run slower, there are better ways than using cursors. like Thread.Sleep(), or WAITFOR DELAY in sql. Put a WAITFOR DELAY '00:10' at the top of each of your procs and you'll have people knocking on your door in notime!

www.elsasoft.org

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-22 : 17:06:07

i'm trying to learn how to create a cursor and trying to figure out if i'm doing right. I didn't know i was making the app slower by using cursor. I was using it because their aren't any build in fuction and also i don't know how to create a function for this exercise to get me the result. i mean to get a summary report. Thankyou for going over my exercise.Is it possible to create a user defined function for this exercise.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-22 : 19:34:17

most of the regular posters here would say that "using a cursor" and "doing it right" are conflicting concepts, and that is prety much always correct. The only exception I have ever come across (where a cursor was more efficient that a set based method) is that of computing running totals. Adam Machanic has a blog post about it: http://www.sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18286.aspx

You can certainly create a function that returns a result set. That's called a table valued function. You can also create a stored procedure to return a result set. Here are the relevant pages in BOL:

http://msdn2.microsoft.com/en-us/library/ms187926.aspx
http://msdn2.microsoft.com/en-us/library/ms186755.aspx -- see the examples in sections B and C on this page for how to return a result set from a function.

www.elsasoft.org

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-22 : 23:12:57

thanks. I will look into it. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources