Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old August 1st, 2003, 01:14 PM
justastef justastef is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2003
Posts: 16 justastef User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Cursor Related problem

Below is the code for the cursor in which I'm using. I'm referencing a table recordset of information and what's occuring is the cursor moves through and creates the tables.

example of data:
tablename columndef
tablea col1 varchar(20) null
tablea col2 varchar(20) null
tableb col1 varchar(20) null
tableb col2 varchar(20) null
tablec col1 varchar(20) null
tablec col2 varchar(20) null

Now this is whats happening... its creating table a and table b but not table c. The reason for this is because when @@fetch_status =0 it will perform the cursor. So when its on the last row of the recordset (tablec col2 varchar(20) null) there is no @table to pull... so it doesn't go back through the cursor to set the @sql to close out and execute to create that last table... I'm wondering if there is any way around this... I was thinking about simply adding after the cursor closed a statement saying

set @sql = @sql + ")"
exec(@sql)

to create that last table, but lets say in the event there are no rows in the recordset, and there are no tables to create... sticking that in there will cause an error. And I've attempted to add another while @@fetch_statement <> 0 or =-1 however, that just causes a continuous loop in my code... unless i did that incorrectly. Anyway heres the code. Maybe someone can think of a way for me to determine within the cursor if its the last row then to close it and execute it.


Declare CreateTableCursor Cursor For
Select table_name, column_definition
from dbo.db_table_information
order by table_name, ordinal_position

--open the cursor
open CreateTableCursor

--reset values of variables
Set @prevtable = ''
Set @sql = ''

Fetch Next from CreateTableCursor into @table, @coldef --fetch row's values into those variables


While @@fetch_status = 0

BEGIN
If object_id(@table) is null

begin

If @prevtable <> @table

BEGIN
If @sql <> ''
BEGIN
Set @sql = @sql + " )" --This statement closes out the @sql string and the create
exec(@sql) --table string executes, creating the table

END
Set @sql = "Create table dbo." + @table + "( " + @coldef

END

If @prevtable = @table
BEGIN
set @sql = @sql + ", "
set @sql = @sql + @coldef
END

set @prevtable = @table

end

Fetch next from createtablecursor into @table, @coldef

END

Close CreateTableCursor
Deallocate CreateTableCursor

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > Cursor Related problem


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway