Stored procedures are a batch of SQL statements that can be executed in a couple of ways. They are similar to User Defined Functions, in that they can use parameters and they are executed by calling them in some custom code. 

Usually stored procedures contain data from your sources, whereas functions gets the data from its parameters.

The issue I want to solve with a Stored Procedure

So I have a exchangerate table. It contains three fields CurrencyCode, ExchangeRate and ToDate.

The fastest way to use such a table is to split it out into individual days.

Split currency rates out on days

There is a issue with this table though. Due to the way this table is set up it wont get the correct dates put in. It will start correctly, but then go from 2007-12-31 to 2006-12-31.

That is an issue. The way to solve this, is to run the part that gets the FromDate on an individual currencycode, do this for each of these and then merge them again.

You can do that with a while loop in a stored procedure.

Set up a stored procedure

Here is the script I came up with.

CREATE PROCEDURE [DSA].[spExchangeRates]
AS
BEGIN
 DECLARE @iCurrencyCode int
 DECLARE @iMaxCurrencyCode int
 CREATE TABLE #TempTable 
 (
  [CurrencyCode] int,
  [ExchangeRate] decimal(38,3),
  [FromDate] datetime,
  [ToDate] datetime
 )
 SET @iCurrencyCode = (SELECT MIN([CurrencyCode]) FROM [ExchangeRates])
 SET @iMaxCurrencyCode = (SELECT MAX([CurrencyCode]) FROM [ExchangeRates])
 WHILE(@iCurrencyCode <= @iMaxCurrencyCode)
 BEGIN
  INSERT INTO #TempTable 
  (
   [CurrencyCode],
   [ExchangeRate],
   [FromDate],
   [ToDate]
  )
  SELECT [CurrencyCode],
  ISNULL([ExchangeRate],0) AS [ExchangeRate],
  ISNULL(LAG([ToDate]) OVER (ORDER BY [DW_Id]),DATEADD(yy, DATEDIFF(yy, 0, [ToDate]), 0)) AS [FromDate],
  [ToDate]
  FROM [ExchangeRates]
  WHERE [CurrencyCode] = @iCurrencyCode

  SET @iCurrencyCode = @iCurrencyCode + 1
 END
 SELECT * FROM #TempTable
 DROP TABLE #TempTable
END

It starts by declaring two integers that are going to give the range from lowest currencycode number to the largest. Then it creates a custom table that will contain the data for each currencycode. Then it creates the while loop. In each iteration It will add the data from that currencycode to the temp table. Finally it will show the temptable and drop it.

To use it simply right click on the stored procedure node and click on Add Stored Procedure.

Then you can paste in the script. Remember to use parameters, so it keeps working when you change names, or warns you if you remove tables.

Then you deploy the procedure.

Use the procedure

To use the procedure you need to call it in a custom action of some sort. Views and stored procedures, doesn't work well together, so instead I will use a custom table insert in a custom table.

Here is the custom table

Here is the script I made for the Custom Table Insert

DECLARE @Temp TABLE 
(
 [CurrencyCode] int,
 [ExchangeRate] decimal(38,3),
 [FromDate] datetime,
 [ToDate] datetime
)
INSERT @Temp EXEC [spExchangeRates]
SELECT [CurrencyCode],
 [ExchangeRate],
 [DateValue] AS [Date] 
FROM @Temp
LEFT JOIN [Date] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]

It uses a temp table again and it inserts the contents of the stored procedure in that. It then uses that data to split the exchange rate out into individual dates.

To add it. Right click on your custom table, go to advanced and click on Add Custom Table Insert. Paste in the script.

Be sure to use parameters, so it will work after name changes and warn when doing deletes.

Deploy/Execute the custom table and see the result.

It should look like this.

Use parameters

If you want to use parameters for this, here below is a way to do this.

The procedure

CREATE PROCEDURE [DSA].[spExchangeRates]
@iCurrencyCode int = NULL,
@iMaxCurrencyCode int = NULL
AS
BEGIN
 CREATE TABLE #TempTable 
 (
  [CurrencyCode] int,
  [ExchangeRate] decimal(38,3),
  [FromDate] datetime,
  [ToDate] datetime
 )
 WHILE(@iCurrencyCode <= @iMaxCurrencyCode)
 BEGIN
 INSERT INTO #TempTable 
 (
  [CurrencyCode],
  [ExchangeRate],
  [FromDate],
  [ToDate]
 )
 SELECT [CurrencyCode],
  ISNULL([ExchangeRate],0) AS [ExchangeRate],
  ISNULL(LAG([ToDate]) OVER (ORDER BY [DW_Id]),DATEADD(yy, DATEDIFF(yy, 0, [ToDate]), 0)) AS [FromDate],
  [ToDate]
 FROM [ExchangeRates]
 WHERE [CurrencyCode] = @iCurrencyCode
 SET @iCurrencyCode = @iCurrencyCode + 1
 END
 SELECT * FROM #TempTable
 DROP TABLE #TempTable
END

The changes are that instead of declaring the first and last currency code i make them parameters.

The script that uses the procedure

DECLARE @1 as INT = (SELECT MIN([CurrencyCode]) FROM [ExchangeRates_V])
DECLARE @2 as INT = (SELECT MAX([CurrencyCode]) FROM [ExchangeRates_V])
DECLARE @Temp TABLE 
(
	[CurrencyCode] int,
	[ExchangeRate] decimal(38,3),
	[FromDate] datetime,
	[ToDate] datetime
)
INSERT @Temp EXEC [DSA].[spExchangeRates]
@iCurrencyCode = @1,
@iMaxCurrencyCode = @2
SELECT [CurrencyCode],
	[ExchangeRate],
	[DateValue] AS [Date] 
FROM @Temp
LEFT JOIN [Date_V] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]

To use the select query that gives me min and max currency code, i have to declare them beforehand. Then i can use them in the stored procedure.