SQL Server Stored Procedure to generate temp table with dynamic columns

At work we required some MI which produced a monthly analysis of certain figures, the recordset I was after would  have whatever we are measuring in the first column, followed by each month of the current year. In my shameful past I would have probaly managed this in my server side code, but I knew this could all be done in SQL and return a nice recordset which only had to be iterated through.  This was the stored procedure:


PROCEDURE [dbo].[spPerilByMonthMI]
--Insurers in form (1,2,3,4,5)

@Insurers nVarchar(1000)
AS

--MONTH
DECLARE @CurrentMonth Integer
SELECT @CurrentMonth = MONTH(getDate())
--YEAR
DECLARE @CurrentYear Integer
SELECT @CurrentYear = YEAR(getDate())

DECLARE @i Integer
DECLARE @SQL nVarchar(1000)
DECLARE @NumberOfPeril Integer
DECLARE @ParamDefinition nVarchar(500)
SET @ParamDefinition = N'@NumberOfPeril Integer OUTPUT';
DECLARE @ValuesSQL nVarchar(100)
DECLARE @ColName nVarchar(100)
DECLARE @SumRowSQL nVarchar(1000)
SET @SumRowSQL =''

CREATE TABLE #MI (
[Peril] varchar(40))

-- Add columns to table
SET @i=1
WHILE (@i1)
BEGIN
SET @SumRowSQL = @SumRowSQL + '+'
END

SET @SumRowSql = @SumRowSql + '['+@ColName+']'

SET @i=@i+1
END

ALTER TABLE #MI ADD [Total] integer NULL

DECLARE @PerilID Integer
DECLARE @Description nvarchar(40)

DECLARE cur CURSOR FOR
SELECT PerilID, [Description] FROM tblPerils
OPEN cur

FETCH NEXT FROM cur INTO @PerilID,@Description

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ValuesSQL = ''
SET @i=1
WHILE (@i1)
BEGIN
SET @ValuesSQL = @ValuesSQL +','
END

SET @ValuesSQL = @ValuesSQL + CAST(@NumberOfPeril As varchar(10))

SET @i=@i+1
END

SET @SQL = 'INSERT INTO #MI VALUES('''+@Description+''','+ @ValuesSQL+',0)'
PRINT(@SQL)
--EXEC(@SQL)
--Total Row

SET @SQL = N'UPDATE #MI SET Total = (SELECT ('+ @SumRowSQL +') FROM #MI WHERE Peril = '''+ @Description +''') WHERE Peril = '''+ @Description +''''

--EXEC(@SQL)
FETCH NEXT FROM cur INTO @PerilID,@Description
END

CLOSE cur
DEALLOCATE cur

SELECT * FROM #MI

DROP TABLE #MI
GO

As can be seen, a loop is set up until it gets to the current month, each iteration generates an add statement to add a column to the temp table. The next loop is going through the table where we are getting the counts to populate each month. This uses the sp_executesql stored procedure to populate the @NumberOfPeril value, which in turn is added to the @Values parameter, which builds up the insert string.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s