Introduction

SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility.

One thing that I try to adhere to — in every language I use — is the Don’t Repeat Yourself, or DRY, principle. In other articles and blogs, I’ve discussed using CROSS APPLY as a way to eliminate repeated expressions. In this one, I want to explore another way to make your code DRYer.

Too many arguments!

One principle from the functional programming world is that a function should have at most 3 or 4 arguments. Any more and the function is probably trying to do too much. In the SQL world, this would apply to both functions and stored procedures. Nevertheless, I see this principle violated with alarming regularity. What can we do about it? Let’s borrow another technique from the functional programming world: partial functions.

The idea sounds simple. Let’s look at an example in Python. (Don’t worry if you don’t know Python. It’s rightly called “the language that’s easy to learn and hard to forget!”). I’ll define a simple function:

OK, that’s not too exciting. However, imagine that, in some section of code, I call myfunc 10 times and for all ten calls, only argument c changes. I’d have to repeat arguments a and b 10 times. What if I could somehow encapsulate them in a custom function so that I only needed to specify argument c? I can do it like this:

def partial_a_b(a, b):
def f(c):
myfunc(a, b, c)
return f

Here, I create a function called partial_a_b that creates a new function each time it is called. The new function encapsulates the values of arguments a and b, just leaving argument c exposed, then returns the function. Then, I use partial_a_b to create another function I call myfunc_c. This snippet yields:

The Answer is 42

In Python, functions are first-class. They can be passed around like other objects and returned from other functions, as above. The function partial_a_b encapsulates arguments a and b. We can also say that the function closes over those arguments and that the partial_a_b is a closure. Closures are a typical way to create partial functions, since they close over some of the arguments while leaving the others exposed.

Writing partial functions is easy in Python, but they are not easy to write in all languages. For example, in an article by Jon Skeet, a .NET guru, he shows how to write them in C#, which is not a functional language. While possible, it is a little complicated. See the references section for a link to that article if you want to know more.

ANSI SQL is another language where writing partial functions are challenging and are dependent on the dialect. Since I’m talking about SQL Server, that means the T-SQL dialect. Functions are not first-class objects in ANSI SQL or T-SQL but we can achieve the same goal using dynamic SQL. Let’s look at an example from a real project I’m working with.

A logging example

Before learning about Partial stored procedures in SQL Server, in case you want to walk through the basics of Stored procedures and understand how they are different from functions, I would recommend you to read these informative articles:

• SQL Server stored procedures for beginners
• Comparing Functions vs Stored Procedures in SQL Server

One system that I help support uses a logging system that is invoked with a stored procedure call. (Note that I’m not arguing the merits of doing logging this way. That could be a subject for another article.) The logger proc has 10 parameters, breaking the rule about 3 or 4 that is typical in functional languages. Furthermore, for a given calling program on a typical run, which may be another stored procedure, 5 or more of those 10 parameters will not change whether the logger is called once or a hundred times. It would be great to be able to build a “partial” procedure that closes over those five, fixed arguments and exposes the rest.

Let’s see how to do that with something simpler than my ten-parameter logger:

The EXEC call produces:

No surprise there.

This little logger doesn’t do much, I’ll admit, but it’s enough to see how to build a partial procedure in T-SQL. Suppose this is called from another stored procedure that needs to log its progress for later analysis. Assume that, for the calling procedure, the first three arguments do not change for a given run. Further, assume that the calling proc has ten places where it wants to call the logger. A typical call might look like:

The first three parameters are variables set elsewhere in the calling proc. Only the last two vary from call to call. What I want is to be able to call something like:

If I can do that, I’ll avoid repeating myself. That is, the code will be DRYer. What I need to do is build a helper proc, which I’ll call GetLogger, that will take in all the arguments that the logger proc has and creates a new, temporary stored procedure called #mylogger. Using a temporary stored procedure makes sense since it is only exposed to the current session, which is the session having the repeated arguments. Here’s the signature of GetLogger:

The sharp-eyed will notice that I’ve added one additional parameter and set the rest to have default values of NULL. I’ll use the @logger parameter to name the partial proc I’ll create and check the NULL values to determine which parameters to close over and which to expose. In the calling proc, I’ll call GetLogger like this:

GetLogger will then create a session-level, temporary procedure called #mylogger, closing over the three non-null arguments and exposing the other two. Let’s see how that works:

All this does is see if the @jobid parameter is specified or not (that is, not NULL). If so, it is appended to a variable called @closed; if not, it is appended to the @exposed variable. A third variable, @passed, is also built up to use in the call to the original logger procedure defined above and pass the exposed parameters to it.

GetLogger does the same processing for all 5 parameters, then builds a CREATE PROC statement:

Now, let’s test this! I call the GetLogger procedure as shown above, which produces one line of output (shown wrapped here for easier reading):

Now let’s call the partial function:

This yields:

This is the same in the original call. The temporary, partial stored procedure works!

Summary

This article shows an approach for building partially stored procedures in T-SQL. This basic approach can be enhanced to suit your needs. For example, in my actual implementation, I add an @debug parameter, which causes the GetLogger proc to create a print statement instead of calling the original procedure. I’ll leave that detail as an exercise for the reader!

You may be wondering if the same technique can be applied to T-SQL functions. In short, it can, with care. Since there is no such thing as a temporary function in T-SQL, you would need to find a way to avoid collisions, in case two sessions tried to create two partial functions at the same time with different parameter values. You see, you have to use a regular function name. You could generate a name for the partial function at runtime, perhaps using the NEWID() function. But then, any place you call your partial function would need to be dynamic SQL, an extra complication (and a second exercise for the reader!)

Others may wonder if I could write a generic stored procedure that would create a partially stored procedure from any other procedure. That would entail some way to call a stored procedure with arbitrary arguments. In pure T-SQL, this is not possible. The system stored procedure I used, sp_executesql, does do this, however. That’s because it is written in CLR code (e.g. C++, C#, etc.) and such functions can indeed take arbitrary parameters.

• Author
• Recent Posts

Gerald Britton

Gerald Britton is a Senior SQL Server Solution Designer, Author, Software Developer, Teacher and a Microsoft Data Platform MVP. He has many years of experience in the IT industry in various roles.

Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author.

You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight

View all posts by Gerald Britton

Latest posts by Gerald Britton (see all)

• Snapshot Isolation in SQL Server - August 5, 2019
• Shrinking your database using DBCC SHRINKFILE - August 16, 2018
• Partial stored procedures in SQL Server - June 8, 2018