SIMPLE CURSOR EXAMPLE

/*

SIMPLE CURSOR EXAMPLE

Generation of a table with nested entities using cursors,

Logical entities are generated in a hierarchical

EJEMPLO SIMPLE DE CURSORES Entities dictionary

Generacion de una tabla con entidades anidades usando cursores,

Se generan entidades logicas en forma jerarquica

*/

--Declare a table
DECLARE @Entionary TABLE  ( 
 [ID]   INT IDENTITY(1,1)  ,
 [Type]   VARCHAR(25)    , 
 [PhisicName]  VARCHAR(255)  ,
 [LogicName]  VARCHAR(255)  ,
 [Parent]  INT   ,
 [Synonym]  INT   ,
 [Help]   xml     
)

-- Var where I take the Identity number of Patern row
DECLARE @RootID   INTEGER  

-- Name of table
DECLARE @object_name VARCHAR(255)

-- Logical name... if you like other name is free to change it
DECLARE @LogicName  VARCHAR(255)

-- Modify Date of  Table, only to show other field
DECLARE @modify_date DATETIME

--Insert the root Row where depends every table name
INSERT INTO 
 @Entionary  (
     [Type]  , 
     [PhisicName] ,  
     [LogicName] ,  
     [Parent] , 
     [Synonym] 
    ) values (  
     'Entity' , 
     'Generic' , 
     'Generic' , 
     NULL  , 
     NULL
    )
    
-- Take the Identity value 
SET @RootID = @@IDENTITY

--Well here declare a cursor, only that
DECLARE Table_cursor CURSOR FOR 
 SELECT 
  name  AS [object_name], 
  modify_date
 FROM 
  sys.objects 
 WHERE 
  type = 'U' 
 ORDER BY 
  name
--Open the cursor 
OPEN Table_cursor
--Fetch the first
FETCH NEXT FROM Table_cursor 
--Use the field values in the declared vars
INTO @object_name , @modify_date
--Loop not eof 
WHILE @@FETCH_STATUS = 0
BEGIN
 --Insert table entity
 SET @LogicName = 'Logic_' + @object_name 
 SET @LogicName = @LogicName + '('+convert( Varchar(10), @modify_date)+')'
 INSERT INTO @Entionary (
     [Type]  , 
     [PhisicName] , 
     [LogicName] , 
     [Parent] , 
     [Synonym]
    ) values (  
     'SubEntity' , 
     @object_name , 
     @LogicName , 
     @RootID , 
     NULL 
    )
 --For each table insert every field
 INSERT INTO @Entionary (
     [Type]  , 
     [PhisicName] , 
     [LogicName] , 
     [Parent] , 
     [Synonym]
    ) 
    select 
     'Field'   ,
     c.name   , 
     'LogicField_' + c.name , 
     @@identity  , 
     NULL  
    FROM 
     sys.columns AS c 
    INNER JOIN 
     sys.objects AS o 
    ON ( 
     o.object_id = c.object_id 
    ) 
    WHERE 
     o.name = @object_name

 --Move next :P
 FETCH NEXT FROM Table_cursor
 INTO  @object_name , @modify_date
--End of cursor
END
--Close an Deallocate cursor 
CLOSE Table_cursor
DEALLOCATE Table_cursor

--I Can't use agregate functions with update from (I Don't know why)... with declared tables, use the temp table
SELECT *  INTO #temp FROM  @Entionary

-- other example for set a xml field, only that
UPDATE #temp SET  [Help] = t.Result
FROM 
 (SELECT ID, CONVERT(XML, '' 
 + CHAR(13) + CHAR(9) + 'MRP'
 + CHAR(13) + CHAR(9) + '23/09/2010'
 + CHAR(13) + CHAR(9) + '' + [LogicName] + ''
 + CHAR(13) + CHAR(9) + '' + [PhisicName] + ''
 + CHAR(13) + CHAR(9) + ''
 + CHAR(13) + CHAR(9) + ''
 + CHAR(13) + ' ' )  AS Result
 FROM #temp ) AS t INNER JOIN #temp ON ( #temp.id = t.id) 

--show the ResultSet
SELECT * FROM #temp

--Drop the temp table
DROP TABLE #temp