Welcome Guest, Not a member yet? Register   Sign In
help with mssql cursors
#1

[eluser]bhakti.thakkar[/eluser]
hi all,
my DB is mssql and i have a stored procedure with a cursor in it.
Code:
CREATE PROCEDURE LimitSelect  
@query CHAR (8000), -- SQL query, it'd better be a SELECT!  
@offset INT, -- start result set from offset  
@limit INT -- limit the result set of the query  
AS  
-- Execute call to declare a global cursor (node_cursor) for the query passed to the SP  
EXEC ('DECLARE node_cursor CURSOR GLOBAL SCROLL READ_ONLY FOR ' + @query)  
  
-- open the global cursor declared above  
OPEN node_cursor  
  
-- tweak the starting values of limit and offset for use in the loop  
SET @offset = @offset + 1  
SET @limit = @limit  
  
-- advanced the cursor to the offset in the result set  
FETCH ABSOLUTE @offset FROM node_cursor  
  
-- counter i  
DECLARE @i INTEGER  
  
SET @i = 0  
  
-- loop until limit reached by counter i  
WHILE (@i < @limit)  
BEGIN  
-- fetch the next row in the result set and advance counter i  
FETCH NEXT FROM node_cursor  
SET @i = @i + 1  
END  
  
-- clean finish  
CLOSE node_cursor  
DEALLOCATE node_cursor

there are records begin fetched individually as below:
when i run my stored procedure i get the result as below:

Project_id Name_VC
1 Henry

Project_id Name_VC
2424 Allen

Project_id Name_VC
32 Solly

Project_id Name_VC
44 Killer

Project_id Name_VC
2 Spiker

Project_id Name_VC
2 Bill

now how will i consolidate this result into a normal recordset instead of separate reordsets. i want someway in CI through which all the data should appear as one big recordset:
Project_ID Name_VC
1 Henry
2424 Allen
32 Solly
44 Killer
and so on

is there any way in CI active recordset function something like above. Otherwise i will have to create temp tables dynamically and insert data dynamic and then select it. lastly drop the #tempTable. but i am worried when the recordset has more than 100000 of records. it will be a hell i suppose

Please some light on it if used mssql cursors

Thanks




Theme © iAndrew 2016 - Forum software by © MyBB