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.

Join with Period table for Period (Trimaster /Monthly ) Summary of Transaction.

For the Monthly/Trimaster/Annual Summary of transaction report, Join with the period table is a very easy alternative.

for example:

tblPeriod

PeriodID int primary,
PeriodName nvarchar(100),
StartDate date,
EndDate Date

tblTransaction

TransactionID int,
TransactionDate Date,
Ledger int,
DrAmount float,
CrAmount float,
Narration

the Period report can be obtained by Joining These table on date between

syntax:

select
  PeridName ,
  sum(isnull(DrAmount,0)) as DrAmount,
  sum(isnull(CrAmount,0)) as CrAmount,
  sum(isnull(DrAmount,0))- sum(isnull(CrAmount,0)) as TotalAmount

from
   tblPeriod left join tblTransaction
on
   tblTransaction. TransactionDate between tblPeriod.StartDate and tblPeriod.EndDate


result will be like:

FirstTrimaster       15000    10000   5000
SecondTrimaster   14500       4500   10000

SQL Server Transactions Management

A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.

We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Types of Transactions

1.                 Implicit Transaction

Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.

2.                Explicit Transaction

Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include Select statement in a transaction.

Transactions Example

1.   CREATE TABLE Department

2.  (

3.   DeptID int PRIMARY KEY,

4.   DeptName varchar(50) NULL,

5.   Location varchar(100) NULL,

6.   )

7.  GO

8.   CREATE TABLE Employee

9.  (

10. EmpID int PRIMARY KEY,

11. Name varchar(50) NULL,

12. Salary int NULL,

13. Address varchar(100) NULL,

14. DeptID int foreign Key references Department(DeptID)

15.)

1.   --Now Insert data

2.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')

3.  GO

4.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)

5.  SELECT * FROM Department

6.  SELECT * FROM Employee

1.   BEGIN TRANSACTION trans

2.  BEGIN TRY

3.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')

4.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)

5.   IF @@TRANCOUNT > 0

6.   BEGIN COMMIT TRANSACTION trans

7.   END

8.  END TRY

9.  BEGIN CATCH

10. print 'Error Occured'

11. IF @@TRANCOUNT > 0

12. BEGIN ROLLBACK TRANSACTION trans

13. END

14.END CATCH

1.   --Now Select data to see transaction affects

2.  SELECT * FROM Employee

3.  SELECT * FROM Department

1.   --Transaction with Save Point BEGIN TRANSACTION trans

2.  BEGIN TRY

3.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')

4.  IF @@TRANCOUNT > 0

5.   BEGIN SAVE TRANSACTION trans;

6.   END

7.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi')

8.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)

9.  IF @@TRANCOUNT > 0

10. BEGIN COMMIT TRANSACTION trans

11. END

12.END TRY

13.BEGIN CATCH

14. print 'Error Occured'

15.IF @@TRANCOUNT > 0

16. BEGIN ROLLBACK TRANSACTION trans

17. END

18.END CATCH

1.   --Now Select data to see transaction affects

2.  SELECT * FROM Employee

3.  SELECT * FROM Department