[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