About Friendship and Using Optional Stored Procedure parameters in Azure Data Factory

(2021-Feb-07) "You've Got a Friend in Me" is a well-known song written by Randy Newman and featured in one of Disney's animated films. If I hadn't had a chance to perform this song at the concert of our local non-professional choir last year before the Covid, I wouldn't realize the deep meaning of some of the lines of this song, my favorite one is, "We stick together and we see it through" :- )

Photo by Zen Chung from Pexels

I don't want to be cheesy and associate the title of this blog post with a friendly relationship between Azure SQL Database and Azure Data Factory, though Microsoft cloud products tend to get along together very well, also there was a recent Around the Clock event focused on Azure SQL and Azure Data Factory: https://aroundtheclockazure.splashthat.com/

Last week I had an opportunity to test this type of mutual relationship between Azure SQL DB and Azure Data Factory (ADF) when an existing SQL stored procedure that was used in my project ADF pipeline, required a new optional parameter. To make a code change in my SQL stored procedure was easy, to get a confirmation that this code wouldn’t affect existing ADF pipelines, i.e. save time for unnecessary regression testing, was a trust test for me.

SQL Code before the change

Let's say my initial Azure SQL database stored procedure looks like this and provides a simple way to retrieve a list of calendar days for a given week number (this is just a sample code I created, not a real project code):

My ADF pipeline lookup activity then executes this [dbo].[sp_get_week_days] stored procedure 

It successfully returns the output result of 7 days for the 1st week of 2021, starting from Sunday of 2020-Dec-27.

SQL Code after the change

After adding a new, but optional, @week_type parameter to my stored procedure, the SQL code looks slightly adjusted:

I could have changed the list of incoming parameters for my stored procedure in the ADF Lookup activity call, and then dedicate some time to make sure that this affected ADF pipeline works as it should, or I keep my trust and make no code changes in ADF and just execute the Lookup activity as it is, i.e now this [dbo].[sp_get_week_days] stored procedure will work with ISO weeks by default and return 7 days of the 1st week of the year 2021 starting from Monday of 2021-Jan-04.

As a result, a mutual friendship between Azure SQL database and Data Factory was confirmed, no ADF code change was required, ISO week’s days were returned and I kept humming a tune of the song, “You’ve Got a Friend in Me” :- )