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 

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]