T-SQL: A Simple Example Using a Cursor

April 23, 2013 by 2 Comments

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.


USE AdventureWorks2008;
GO

SELECT BusinessEntityID, Name
 FROM Sales.Store;

Which looks something like this:

SimpleCursor1

Now to convert it to a cursor, instead of just a select statement.

Step 1: Declare variables to hold the output from the cursor.

</p>
DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

Step 2: Declare the cursor object;


DECLARE @BusinessCursor as CURSOR;

Step 3: Assign the query to the cursor.


SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

Step 4: Open the cursor.


OPEN @BusinessCursor;

Step 5: Fetch the first row.


FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

Step 5: Loop until there are no more results.  In the loop print out the ID and the name from the result set and fetch the net row.


WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

Step 6: Close the cursor.


CLOSE @BusinessCursor;

Step 7: Deallocate the cursor to free up any memory or open result sets.


DEALLOCATE @BusinessCursor;

Now putting it all together:


DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

SimpleCursor2

This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server. The example shown was run on SQL Server 2008, and works the same on SQL Server 2005 or 2012.

Enjoy!

Filed under SQL 2008, SQL 2012, TSQL Tagged with , , , , , , ,

Recursive Scalar Function in T-SQL

April 22, 2013 by Leave a Comment

In my Common Table Expressions presentation the topic of recursion often comes up, but for scalar functions in T-SQL, it might not be as common.
This article has been written to show how a scalar function in SQL Server can call itself, thus being considered recursive.

What is the Fibonacci Sequence

The Fibonacci Sequence is a classic example that is used to demonstrate recursion.

By definition, the first two numbers in the Fibonacci sequence are 0 and 1, and each subsequent number is the sum of the previous two.

For instance, the following are Fibonacci Numbers:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584, 4181, 6765, 10946, 17711, 28657, 46368, 75025, 121393, 196418, 317811, 514229, 832040, 1346269, 2178309, 3524578, 5702887, 9227465, 14930352, 24157817, 39088169, 63245986, 102334155, 165580141, 267914296, 433494437, 701408733 …

Fibonacci Sequence as a Computer Science Challenge

Often times calculating the Fibonacci Sequence is used as a computer science puzzle, or programming interview question to see if you understand recursion.  It is very simple to do in any programming language that supports recursion.

What is Recursion

Recursion is a programming concept where a function, procedure or section of code calls itself. For instance in the following T-SQL example, the scalar function Fibonacci calculates the Fibonacci sequence using recursion, by calling itself.  This is accomplished by calling the function name inside of the body of the function.


CREATE FUNCTION dbo.Fibonacci (@Num integer, @prev integer, @next integer)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @returnValue as VARCHAR (4000) = cast(@prev as varchar(4000));
IF (@Num > 0)
BEGIN
IF (LEN(@returnValue) > 0)
BEGIN
SET @returnValue = @returnValue + ',';
END
SET @returnValue = @returnValue + dbo.Fibonacci(@Num - 1, @next, @next + @prev) ;
END

RETURN @returnValue;
END
GO

To call the function you simply include in a select statement, with the first parameter being the number of Fibonacci numbers to calculate, and the second and third parameters are always 0 and 1. The second and third parameters are set to 0 and 1 to prime the recursive function, and are used internally to pass the recursive call the current and previous values.

select dbo.Fibonacci(10, 0, 1);

Which produces the following output using SSMS:
FIbonacci_Scalar

For more details on the CTE version of Fibonaci take a look at my earlier post.

Enjoy!

Filed under TSQL Tagged with , , , , , , , , ,

SQL Server 2012 IIF Statement

April 3, 2013 by 1 Comment

SQL Server 2012 introduces the IIF statement. It is very similar to the IIF statement in MS Access.

I have extracted the IIF part of my presentation on Whats new in SQL Server 2012, and turned it into a video with a demo of how to use the IIF statement. The IIF statement is a quick shortcut to simple CASE statements.

Here is the T-SQL references in the video:

USE [Master];
CREATE DATABASE [tsql2012];
GO

USE [tsql2012];

-- Table to be used by Over Clause Rows/Range
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);

USE [tsql2012];
-- first lets look at the REVENUE table
SELECT *
FROM Revenue;

-------------------------------------------------------------------

-- IIF
-- http://stevestedman.com/?p=1578
-- assume we want to display an indicator to see if we are above
-- or below average. First we start with the average over departmentID
SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE
ORDER BY DepartmentID, year;
-- without IIF using the CASE statement we would get the following
-- http://stevestedman.com/?p=1578
SELECT Year, DepartmentID, Revenue, AverageRevenue,
CASE WHEN Revenue > AverageRevenue
THEN 'Better Than Average'
ELSE 'Not Better'
END as Ranking
FROM (SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE ) as t
ORDER BY DepartmentID, year;

-- now the same functionality using IIF and simplifying the code
-- http://stevestedman.com/?p=1578
SELECT Year, DepartmentID, Revenue, AverageRevenue,
iif(Revenue > AverageRevenue, 'Better Than Average', 'Not Better') as Ranking
FROM (SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE ) as t
ORDER BY DepartmentID, year;

----------------------------------
SELECT IIF(3=2,'true',NULL) -- ERROR ?

----------------------------------

SELECT IIF(1=7,NULL,NULL) -- ERROR !
--Do you think that IIF actually uses the CASE statement?

Filed under SQL 2012, TSQL 2012 Tagged with , , , , , , ,

TSQL Pivot Table

June 22, 2012 by Leave a Comment

Here is a quick sample of how to implement a pivot table in TSQL for SQL Server. The example below creates a database called pivot, you probably already have your own database to work in. Then it creates a table called REVENUE and fills it in with department revenue for just over a 10 year period. Then you see a couple simple select statements followed by a SELECT statement that pivots the data.


USE [Master];
set statistics io off;

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'pivot')
BEGIN
ALTER DATABASE [pivot] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [pivot];
END
CREATE DATABASE [pivot];
GO

USE [pivot];

-- Table to be used for demo
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);
USE [pivot];
-- first lets look at the REVENUE table

SELECT *
FROM Revenue;
SELECT DepartmentId, Year, Revenue
FROM Revenue;

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

Just to recap, here is the actual pivot code, and the output that it produces.

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

I hope you find this useful as an example of working with PIVOT tables in SQL Server using the select statement in TSQL.

Filed under TSQL Tagged with , , , ,

Using the OUTPUT Clause in TSQL for Auditing

March 28, 2012 by Leave a Comment

The OUTPUT clause is often times underappreciated by the TSQL programmer.  There are many really interesting things that you can do with the OUTPUT clause, from inserting to two tables with one statement or to replace the SCOPE_IDENTITY option.  For this posting I am looking into using the OUTPUT clause for auditing.

By auditing in this example, I specifically mean tracking what was changed, and when it was changed.

In the sample below I create a database called output_demo, create a called Departments, add a few rows, create another table called DeptAudit then update Departments and insert to DeptAudit in one statement.


USE [Master];

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'output_demo')
BEGIN
 ALTER DATABASE [output_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 DROP DATABASE [output_demo];
END
GO
CREATE DATABASE [output_demo];
GO

USE [output_demo];
-- create a table to use for the demo
CREATE TABLE [Departments] (
 id int, --would normally be an INT IDENTITY
 department VARCHAR (200),
 parent int
 );

CREATE TABLE [DeptAudit] (
 id int, --would normally be an INT IDENTITY
 old_department VARCHAR (200),
 new_department VARCHAR (200),
 parent int,
 ModificationDate DATETIME
 );
-- insert top level departments
insert into [Departments] (id, department, parent)
values (1, 'Camping', null),
 (2, 'Cycle', null),
 (3, 'Snowsports', null),
 (4, 'Fitness', null);

-- now some sub-departments for camping
insert into [Departments] (id, department, parent)
values (5, 'Tents', 1),
 (6, 'Backpacks', 1),
 (7, 'Sleeping Bags', 1),
 (8, 'Cooking', 1);

select * from Departments;

-- here's the magic to update one table and insert into another in one statement

update dept
 set dept.department = 'Camp Kitchen'
output [deleted].id, [deleted].department, 
       [inserted].department, [deleted].parent, GETUTCDATE()
 INTO DeptAudit
 from Departments dept
 where dept.id = 8;

select * from Departments;
select * from DeptAudit;

Which generates this output.

Where you can see that the update statement to change ‘Cooking’ to ‘Camp Kitchen’ was logged into the DeptAudit table showing both the old and the new value.

A similar process could be logged on INSERT, UPDATE, DELETE, or MERGE in order to track anything that is being changed in a table.

 

Filed under TSQL Tagged with , , , , , , ,

TSQL Analytic Functions LEAD and LAG

March 27, 2012 by 5 Comments

SQL Server 2012 introduces 8 new analytic functions. This post will cover 2 of them LEAD and LAG, which can be used to reference a value in the row before or the row after the current row.

If you have read my OVER clause articles you will recognize the REVENUE table. Just a table listing department revenue year over year for 3 departments.

USE [Master];

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'analytics_demo')
BEGIN
	ALTER DATABASE [analytics_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [analytics_demo];
END
GO
CREATE DATABASE [analytics_demo];
GO

USE [analytics_demo];

-- same Revenue Table used in previous examples of the OVER clause
-- http://stevestedman.com/?p=1454

CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
 (1,20000,1999),(2,60000,1999),(3,50000,1999),
 (1,40000,2000),(2,40000,2000),(3,60000,2000),
 (1,30000,2001),(2,30000,2001),(3,70000,2001),
 (1,90000,2002),(2,20000,2002),(3,80000,2002),
 (1,10300,2003),(2,1000,2003), (3,90000,2003),
 (1,10000,2004),(2,10000,2004),(3,10000,2004),
 (1,20000,2005),(2,20000,2005),(3,20000,2005),
 (1,40000,2006),(2,30000,2006),(3,30000,2006),
 (1,70000,2007),(2,40000,2007),(3,40000,2007),
 (1,50000,2008),(2,50000,2008),(3,50000,2008),
 (1,20000,2009),(2,60000,2009),(3,60000,2009),
 (1,30000,2010),(2,70000,2010),(3,70000,2010),
 (1,80000,2011),(2,80000,2011),(3,80000,2011),
 (1,10000,2012),(2,90000,2012),(3,90000,2012);

 --just double check the table to see what's there for DepartmentID of 1
 select DepartmentID, Revenue, Year
 from REVENUE
 where DepartmentID = 1;

-- Using LAG
-- http://stevestedman.com/?p=1513

select DepartmentID, Revenue, Year,
       LAG(Revenue) OVER (ORDER BY Year) as LastYearRevenue
 from REVENUE
 where DepartmentID = 1
 order by Year;

With LAG you can grab a value from the previous row, or previous value in the set as as set by the OVER (ORDER BY ) clause.

Now for LEAD which allows you to pull from the next row in your set.

select DepartmentID, Revenue, Year,
       LAG(Revenue) OVER (ORDER BY Year) as LastYearRevenue,
       LEAD(Revenue) OVER (ORDER BY Year) as NextYearRevenue
  from REVENUE
 where DepartmentID = 1
  order by Year;

Which produces the following output adding in a new column called NextYearRevenue

So far, this is pretty exciting as a way to grab a value from the previous year or next year. Now to use it!

Calculating the Year Over Year Delta in the revenue numbers. Sure you could do this with a CTE and a lot of recursion, but it is just so much easier using LAG.

--So how do we calculate the difference between last year's
-- numbers and this years numbers

select DepartmentID, Revenue, Year,
       LAG(Revenue) OVER (ORDER BY Year) as LastYearRevenue,
       Revenue - LAG(Revenue) OVER (ORDER BY Year) as YearOverYearDelta
  from REVENUE
 where DepartmentID = 1
  order by Year;

and here is what you get.

That’s it for LEAD and LAG, but keep in mind these are just building blocks for the analytic functions. More to come on the analytic functions in SQL 2012.

I hope you find LEAD and LAG in SQL Server as useful as I have.  Just remember “Statistics can be made to prove anything—even the truth.”

Filed under SQL 2012, TSQL 2012 Tagged with , , , , , , ,

← Older posts