Temperory Table and Cursor and while loop to Fill and generate Custom report in Sql.

This Type of report are very useful in case of slow network. As all the customization of data takes place at Database server end. there is only one fetching of data from the database server hence less time needed to generate the report.

This Concept uses Temporary Table to Get the data from the different Transaction and detail table.  

The Temporary table have a scope of only equal to the scope of it Process id. When creating a local temporary table the table is persisited in the tempdb, but its scope is specific to the spid of the process that created it, ie. you can open up multiple query windows within sql server management studio and create a local temporary table,
CREATE TABLE #temp(
col1       INT

),

and this will not cause any issue as the table is specific to the process id.

 Cursor  gets the data (single column at a time) from some select statement which is stored in the local variable of same data type as select statement. For While loop.

Defining Cursor:

Declare Row Cursor for
select distinct col2 from  tblname ;
open Row;

....use the row data in statements here.

Close Row
Deallocate Row 

While Loop using data fetched from Cursor 

syntax:

 Declare Row Cursor for
select distinct col2 from  tblname ;
open Row;

FETCH NEXT FROM Row into @Row  

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #temp col1  values(@Row)

 FETCH NEXT FROM Row into @Row

END

 Close Row
Deallocate Row

The Complete Code Should Look Like:

CREATE TABLE #temp(
col1       INT
);

 Declare Row Cursor for
select distinct col2 from  tblname ;
open Row;

FETCH NEXT FROM Row into @Row  

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #temp col1  values(@Row)

 FETCH NEXT FROM Row into @Row

END

 Close Row
Deallocate Row
Drop table #temp -- Donot forget to clear un wanted temp data.