Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Using SQL Cursors

Using a Cursor in T-SQL

This tutorial describes how to use a cursor to iterate through rows of data to construct a single entry into a table of order IDs and Totals for each Sales Person from a view. Using the AdventureWorks database, a view, a table and code will be created to produce the final deliverable: an Excel report containing the compressed data in a simple, easy to read format.

Step 1: Create the SQL View to Retrieve the Data

Using the AdventureWorks database, the tables: SalesPerson and SalesOrderHeader, Contact and Employee, the view to retreive the data can be created.

CREATE VIEW [dbo].[vw_SalesPersonOrders]
AS
SELECT Sales.SalesPerson.SalesPersonID, Sales.SalesOrderHeader.SalesOrderID, Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.OrderDate,
Person.Contact.FirstName, Person.Contact.LastName
FROM Sales.SalesPerson
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
INNER JOIN HumanResources.Employee ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID
INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID

The data from the created View looks like this:

Sql View Data Sales by Sales Person

Step 2: Construct the SQL Cursor Code

Create code to iterate through the SQL View line by line to construct a single entry for each Sales Person.

Code some variables to hold the current and previous data, including the salesId, orderID and totals. The previous values will become the current values after each pass throught the cursor. The salesID will be compared through each iteration. When a different Sales Person ID is found, an Insert of the concatenated orders and totals will be made into a table.

Declare @salesid_curr int
Declare @salesid_prev int
Declare @salesName_prev varchar(100)
Declare @salesName_curr varchar(100)
Declare @orderid_curr int
Declare @orderid_prev int
Declare @total_curr money
Declare @total_prev money

Declare @total varchar(7000)
Declare @orderIDs varchar(3000)
Declare @salesID int

Include a statement to Truncate the table (to be created) before doing any work.

TRUNCATE TABLE dbo.SalesPersonOrders

Write the code to Declare and Open a Cursor and then Fetch the data into the cursor while the @@FETCH_STATUS = 0.

Notes: the code is:
- Iterating through the data using NoLock since no transactions are taking place

- Using Else If statements to capture row 1, rows with the same ID and rows with different IDs and appending or INSERTing the date appropriately.

- Concatenating the information into OrderIDs & TotalAmts using a Convert function and Char (10) + Char (13) to provide a line break when the data is imported to Excel

--declare the cursor
DECLARE sales_cursor CURSOR FOR
  SELECT SalesPersonID, FirstName + ' ' + LastName, SalesOrderID, TotalDue
  FROM vw_SalesPersonOrders with(nolock)
  WHERE OrderDate BETWEEN '2004-05-01' and '2004-06-30'
  ORDER BY SalesPersonID, SalesOrderID

  OPEN sales_cursor

--set the salesIds to 0
  Set @salesid_curr=0
  Set @salesid_prev=0

--get the first record from the cursor and populate the variables
FETCH NEXT FROM sales_cursor
INTO @salesid_curr, @salesName_curr, @orderid_curr, @total_curr

--iterate through the cursor until last row
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (@salesid_prev = 0) --get the 1st record
     BEGIN
      SET @salesid_prev = @salesid_curr
      SET @salesName_prev = @salesName_curr
      SET @orderid_prev = @orderid_curr
      SET @total_prev = @total_curr
      SET @salesID = @salesID_curr
      SET @orderIDs = IsNull(@orderIDs,'') + CHAR(10) + char(13) +
       convert(varchar(6),@orderid_curr)
      SET @total = IsNull(@total,'') + CHAR(10) + CHAR(13) + CONVERT(varchar(15), @total_curr)
     END

    ELSE IF (@salesid_prev = @salesid_curr)
     BEGIN
      SET @salesid_prev = @salesid_curr
      SET @salesName_prev = @salesName_curr
      SET @orderid_prev = @orderid_curr
      SET @total_prev = @total_curr
      SET @salesID = @salesID_curr
      SET @orderIDs = IsNull(@orderIDs,'') + char(10) + char(13) +
       convert(varchar(6),@orderid_curr)
      SET @total = IsNull(@total,'') + CHAR(10) + CHAR(13) + CONVERT(varchar(15), @total_curr)
     END

   --compare and if different, do an INSERT
    ELSE IF (@salesid_prev <> @salesid_curr and @salesid_prev > 0)
     BEGIN
      INSERT INTO dbo.SalesPersonOrders
      (SalesPersonID, SalesPersonName, SalesOrderID, TotalDue)
        VALUES (@salesID, @salesName_prev, @orderIDs, @total)

     SET @salesid_prev = @salesid_curr
     SET @salesName_prev = @salesName_curr
     SET @orderid_prev = @orderid_curr
     SET @total_prev = @total_curr
     SET @salesID = @salesID_curr
     SET @orderIDs = convert(varchar(6),@orderid_curr)
     SET @total = CONVERT(varchar(15), @total_curr)
    END

FETCH NEXT FROM sales_cursor
INTO @salesid_curr, @salesName_curr, @orderid_curr, @total_curr
END

--catches the last SalesPerson
     INSERT INTO dbo.SalesPersonOrders (SalesPersonID, SalesPersonName, SalesOrderID, TotalDue)
     VALUES (@salesID, @salesName_prev, @orderIDs, @total)

Add code to close the cursor for clean up

CLOSE sales_cursor DEALLOCATE sales_cursor

Step 3: Create the Data Table

Write T-SQL Code to create the table receiving the INSERTs.

CREATE TABLE dbo.SalesPersonOrders(
  SalesPersonID int NULL,
  SalesPersonName varchar(100) NULL,
  SalesOrderID varchar(3000) NULL,
  TotalDue varchar(7000) NULL )

Step 4: Create a SQL Server Connection from Excel

Create a new connection from Excel to the dbo.SalesPersonOrders table to retrieve the results as below. Go to the Data tab and select From SQL Server.

Sql Server Connection from Excel

The Data Connection Wizard will open. Enter the name of the SQL Server to connect to the table.

Data Connection Wizard

Select the database name and then highlight the table name and click Finish

SQL Database and Table Selection in Excel

Click OK on the Pop up window to import the data to the first cell in the corner, $A$1.

Import Data from SQL Server to Excel

Right click on the SalesOrderID and Total Due columns and select Format Cells. Click on the Alignment tab and click the Wrap Text checkbox to format the results as shown here.

SQL Cursor Data in Excel

 

This concludes this tutorial on how to concatenate data using a cursor.