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.

SQL Server Function to return date from known number of working days

At work we required a way to get a date in the future from a known date + x number of working days.  With help from google searches of other solutions  I wrote this function to take in a datetime and number of days which was looped through, incrementing the date a day at a time and  adding to the count by one if  if the date was neither a weekend day nor a bank holiday. The table tblNonWorkingDays is just a single field of dates that are bank holidays.

I’m sure there are easier ways and if someone knows of one I’d welcome any input but I like the simplicity of this…


FUNCTION [dbo].[f_WorkingDays] (@InputDate datetime, @Days int)
RETURNS DATETIME AS
BEGIN

DECLARE @Count AS INT
DECLARE @NonWorking AS INT
DECLARE @DateIncrement AS INT
DECLARE @DayNumber AS INT
DECLARE @ReturnDate AS DATETIME

SET @ReturnDate = @InputDate
SET @Count = 0

SET @DateIncrement = 1

IF @Days < 0
BEGIN
SET @DateIncrement = -1
END

WHILE @Count < ABS(@Days)
BEGIN
SET @ReturnDate = DATEADD(DAY, @DateIncrement, @ReturnDate)

— Check if Weekday
SET @DayNumber = DATEPART(WEEKDAY, @ReturnDate)
IF (@DayNumber > 1 AND @DayNumber < 7) –Sat or Sun
BEGIN

— if weekday then check if not a bank holiday

SELECT @NonWorking = (
SELECT COUNT(NonWorkingDate)
FROM [tblNonWorkingDates]
WHERE NonWorkingDate =
(SELECT CONVERT(VARCHAR(4), DATEPART(YEAR, @ReturnDate)) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(MONTH, @ReturnDate)) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(DAY, @ReturnDate)) + ‘ 00:00:00.000’))

IF @NonWorking = 0
BEGIN
SET @Count = @Count + 1
END

END

END –WHILE

RETURN @ReturnDate

END