DEFINE CURSOR Results Problem - 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

Transact-SQL (2000)

DEFINE CURSOR Results Problem

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2002-10-14 : 23:17:31

Is there a problem with using a JOIN clause in the DEFINE CURSOR statement?

The following code outputs the correct number of rows, but incorrectly outputs the data from the last row for all rows:


=================================
USE pubs

DECLARE @TableName SYSNAME
DECLARE @PKName varchar(30)
DECLARE @TN varchar(30)
 
DECLARE tnames_Cursor CURSOR FOR
select k.TABLE_NAME, COLUMN_NAME
   from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
      JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
         ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME
   WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND k.TABLE_NAME <> 'dtproperties'
   ORDER BY k.Table_Name

OPEN tnames_Cursor
PRINT 'Num Rows: ' + Convert(varchar(5),@@CURSOR_ROWS)

FETCH NEXT FROM tnames_Cursor INTO @TableName,@PKName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2 )
   BEGIN
      SELECT @tablename = RTRIM(@tablename)
      PRINT 'Table: ' + @TableName + '   PK: ' + @PKName  
   END

   FETCH NEXT FROM tnames_Cursor INTO @TableName,@PKName 

END

CLOSE tnames_Cursor
DEALLOCATE tnames_Cursor
============================================

This produces the following output:

========================
Num Rows: 12
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
Table: titles PK: pub_id
=============================

However, if you execute just the SELECT statement in the Query Analyser, it produces the following:

 
===============================
authors     au_id
employee    emp_id
jobs        job_id
pub_info    pub_id
publishers  pub_id
sales       ord_num
sales       stor_id
sales       title_id
stores      stor_id
titleauthor au_id
titleauthor title_id
titles      title_id
============================

Can anyone explain this, or tell me how to fix the DEFINE CURSOR code?

TIA.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-15 : 06:08:47

works ok for me v2000.
What version/service pack do you have?
(Are you sure you ran this code)

==========================================
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.

Apollois
Starting Member

49 Posts

Posted - 2002-10-15 : 11:41:07

quote:
What version/service pack do you have?
(Are you sure you ran this code)

VER: SQL Server 7.00.623
SP: Don't know SP. How do I determine?
Yes, I have ran the code many times, using both SS7 QA and SS2K QA, but on the SS7 DB.

I've tried this on several DBs. Same type of results.

Any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 12:08:37

Well, I don't know why you need a cursor for this, but see if this works:

FETCH NEXT FROM tnames_Cursor INTO @TableName, @PKName
WHILE @@FETCH_STATUS=0
BEGIN
      PRINT 'Table: ' + RTRIM(@TableName) + '   PK: ' + @PKName  
      FETCH NEXT FROM tnames_Cursor INTO @TableName, @PKName 
END

CLOSE tnames_Cursor
DEALLOCATE tnames_Cursor

You could get the same output from:

SELECT 'Table: ' + k.TABLE_NAME + ' PK: ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND k.TABLE_NAME <> 'dtproperties'
ORDER BY k.Table_Name
1
And here's a list of SQL Server version numbers:

http://www.sqlteam.com/item.asp?ItemID=8318

Also look at the comments for that article, there have been some additions for various hotfixes.

Edited by - robvolk on 10/15/2002 12:11:13

Apollois
Starting Member

49 Posts

Posted - 2002-10-15 : 13:43:59

quote:

Well, I don't know why you need a cursor for this, but see if this works

Thanks rob for your suggestion and help. After I applied SS7 SP4 (It was at SP2), it fixed the problem. The code now outputs the correct data.

The reason for using a cursor is that this is the beginning of a script to autogenerate a trigger script for each table. This script needs both the tablename and the primary key name. After I get the cursor, I need to go through a loop on each table to output the trigger script.

Is there a better way to do this?

TIA.

Jim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 14:37:43

Not really, when I think about it. I've never gotten into automatically writing trigger code so I never thought of doing it that way.

If the trigger code is small enough and generic enough then you can extend the string concatenation a little and try something like this:

DECLARE @triggerSQL varchar(8000), @sql varchar(8000)
SELECT @triggerSQL='CREATE TRIGGER <table>TriggerName ON <table> FOR INSERT, UPDATE AS
UPDATE T SET LastUpdated=GetDate()
FROM <table> T INNER JOIN inserted I ON T.<PKColumn>=I.<PKColumn>;GO;'
SET @sql=''
SELECT @sql=@sql + Replace(Replace(@triggerSQL, '<table>', k.TABLE_NAME), '<PKColumn>', COLUMN_NAME)
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND k.TABLE_NAME <> 'dtproperties'
ORDER BY k.Table_Name
EXEC (@sql)

It's a little messy, but basically it creates the code for all of the triggers in one fell swoop, as one batch of SQL, and allows you to execute it as one batch. The <table> and <PKColumn> tags are just placeholders/tokens for where to plug in the table and key column names. The Replace functions actually replace them with the values from the INFORMATION_SCHEMA views. If you PRINT the @sql variable you should see the whole shebang as one large command.

This won't work if you have 100+ tables, and probably won't perform better than the cursor solution would. You can probably alter this to generate one trigger at a time only, but again it wouldn't be much different than the cursor.

Apollois
Starting Member

49 Posts

Posted - 2002-10-15 : 14:48:23

Thanks for your help and suggestions Rob.

Since I've already got the cursor script working (after SP4

), I think I'll continue along that line. I think it will be easier to modify/debug the trigger script.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources