Transact SQL Question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Transact SQL Question

Author

Topic

GregLuce
Starting Member

4 Posts

Posted - 2002-11-27 : 09:37:09

I'm trying to learn to run T-SQL scripts in Query Analyzer like I can with Oracle in SQL Plus. In the Books Online I found a nice example of a cursor loop running a report against the pubs db. I paste this script into Query Analyzer and hit go and get 5 different errors. Here's the script, you can find it in books online with keyword "cursors, Transact-SQL":
SET NOCOUNT ON

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

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

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: " +
@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
Why is it not possible to run this in Query Analyzer against the pubs db?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-27 : 09:49:00

What are the errors - they should tell what the problem is and which line it's on

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith
Starting Member

24 Posts

Posted - 2002-11-27 : 11:51:24

Try turning all the double quotes to single quotes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources