|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
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
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Databases > Database Management > Cursor Related problem |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|