Advanced Stored Procedures

Summary of Video

This video demonstrates using dynamic SQL and database cursors to perform relatively complex SQL queries.

Prerequisites

The lecture uses the Lookup table example Lecture – Lookup Tables Part 1

The Data Dictionary used in this lecture is available at http://www.census.gov/acs/www/Downloads/data_documentation/pums/DataDict/PUMS_Data_Dictionary_2009-2011.pdf

The Data used in this lecture is uploaded here FL_Housing_Data_2006.zip

Video Link

http://www.youtube.com/watch?v=B9zKi8H_IUs

Support Materials

ALTER PROCEDURE sp_ACRReport (
 @ColCategory NVARCHAR(50)
)
AS
BEGIN 
-- This report creates a count of entries of a specific field
-- specified by @RowCategory, broken down by @ColCategory
-- We are going to use a CURSOR for Rows   
DECLARE Col_Cursor CURSOR FOR   
    SELECT [Name], [Description]
    FROM dbo.Codes 
    WHERE [CategoryName] = @ColCategory
-- Next we will create a place to put the cursor results
DECLARE @CatDescription NVARCHAR(200)
DECLARE @Query NVARCHAR(500)
DECLARE @ColCodeName NVARCHAR(50)
DECLARE @ColCodeDescription NVARCHAR(200)
SET @CatDescription = (SELECT [Description] FROM Categories WHERE Name = @ColCategory)
-- Cursors are Opened and Closed    
-- For each row we enumerate, we want the 
OPEN Col_Cursor
FETCH NEXT FROM Col_Cursor INTO @ColCodeName, @ColCodeDescription
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  PRINT ''
  PRINT '-- ' + LEFT(@CatDescription, 50) + ' = ' + @ColCodeDescription + '  --'
  SET @Query ='SELECT LEFT([Codes].[Description], 50), COUNT(*) As ''Number Units'' 
    FROM RawData, Codes, Categories 
    WHERE RawData.ACR = Codes.Name
    AND   Codes.CategoryName = ''ACR''
    AND   RawData.' + @ColCategory + ' = ''' + @ColCodeName + ''' 
    GROUP BY [Codes].[Description]'  
  EXECUTE(@Query)
  FETCH NEXT FROM Col_Cursor INTO @ColCodeName, @ColCodeDescription

END
CLOSE Col_Cursor
DEALLOCATE Col_Cursor  
END

Example of a filtered query using this database

SELECT [Codes].[Description], COUNT(*) As 'Number Units' FROM RawData, Codes, Categories 
    WHERE RawData.ACR = Codes.Name
    AND   Codes.CategoryName = 'ACR'
    AND   RawData.FS = '1' 
    GROUP BY [Codes].[Description]