Follow

How to use a Stored Procedure

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.

1.PNG

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

Split currencyrates 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.

2.PNG

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.

3.PNG

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.

4.PNGThen you deploy the procedure.

5.PNG

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

6.PNG

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.

7.PNG

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

8.PNG

Deploy/Execute the custom table and see the result.

It should look like this.

9..PNG

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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.