| Author |
 Topic  |
|
|
tadin
Yak Posting Veteran
63 Posts |
 Posted - 04/20/2007 : 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
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
tadin
Yak Posting Veteran
63 Posts |
Posted - 04/21/2007 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 04/21/2007 : 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 - 04/22/2007 : 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. |
Edited by - tadin on 04/22/2007 17:08:14 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
|
|
tadin
Yak Posting Veteran
63 Posts |
Posted - 04/22/2007 : 23:12:57
|
| thanks. I will look into it. Thanks again. |
|
|
| |
Topic |
|
|
|
cursors help-pubs database
