SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 cursors help-pubs database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tadin
Yak Posting Veteran

63 Posts

Posted - 04/20/2007 :  11:41:17  Show Profile  Reply with 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.
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

2165 Posts

Posted - 04/20/2007 :  12:56:28  Show Profile  Reply with Quote
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
Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 04/21/2007 :  22:21:57  Show Profile  Reply with Quote
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

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/21/2007 :  22:50:53  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 04/22/2007 :  17:06:07  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/22/2007 :  19:34:17  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 04/22/2007 :  23:12:57  Show Profile  Reply with Quote
thanks. I will look into it. Thanks again.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000