Functional programming in T-SQL

I discovered last year that functional programming is a programming paradigm applicable to T-SQL, and that T-SQL in very well suited for it.  I've done very interesting achievements with it.

For most of us our programming paradigm is “imperative programming” or “procedural programming”. This is the way programming is taught today. Most language like C#, Java, Javascript are used in this way. Lately we see some features specific to functional programming being introduced in them. T-SQL is a poor imperative language, but surprisingly a much better one when used in a "functional programming" way.

For those who are not familiar with what is “functional programming”, I will try to say in few words what it is.

A simplistic definition of functional programming is a way to program by using functions and expressions, and avoid the use of variables (at least variables that get modified once initialized). This is a programming model that is mandatory in some languages, especially languages used for artificial intelligence. I could talk of some old languages like Lisp, APL, but there are also new ones like F#, Clojure. My programming culture about this subject is limited and they are many others for which I can’t tell which ones are best.

I’ll ask to the functional programming experts to be indulgent for what I can say about it, I’m not an expert in the field of functional programming, and T-SQL is not a pure functional programming language.  Probably it doesn't allows either many of the things that it requests to be one. It is first a declarative language and not very well suited for imperative programming.  However the results for me as a T-SQL programmer of applying only a few concepts related to functional programming were astonising.

There are two articles in Wikipedia about this https://en.wikipedia.org/wiki/Functional_programming and https://en.wikipedia.org/wiki/Procedural_programming but it is not necessary to read all this to grasp the idea.  The T-SQL samples I will add later will prove to be a good introduction to the concept.

One of the reason functional programming is praised, is because the code is very predictable in its behavior. The reason for that is that there are no variables that change states and hence multiply algorithm states, one initialized.

For those familiar to T-SQL, I can say that I developed a very big script (around 9000 lines), whose function is to merge databases from different customers, by application types. It's goal is to solve key conflict automatically or by interaction with users through generated tables.  It's behavior is parametrized through CTE that express business rules for each application in the form a rows.  Theses business rules govern T-SQL code generation for each merge case, or help specifying exceptions from the generic process.  This script has no variables, no IF Then Else statements, only one main loop, and a hanful of loops in few utility procedures that help support functional programming in T-SQL.

I’m also planning to rewrite some other big scripts and I'm quite confident to reduce code size by 60%, and make them a lot easier to maintain.

There’s a lot T-SQL code generation, but thanks to functional programming, it makes it very readable, and very easy to modify. T-SQL code generation is heavily based on table expressions (CTE) functions and SQL templates strings supplied in CTE. This makes easier to define business logic as data through the use of CTE that defines and put together business rules as tables rows.

Most of the stored procedures in it, were used mainly as way to put together processing steps to document them. They weren't necessary.  These procedures have no parameters.

While the process of merging database by product type is a quite complex, it is still very readable. Most of the code parts are individually testable, by selecting and running them, even parts in stored procedures, since there are no parameters dependencies. Select parts of the code, run it, and it is possible to see its results and its behavior. 

There is some persistence of state execution through a few tables that marks some large steps progress, but I follow the same functional programming rule as I would for variables.  Once content is initialized, I don't modify them.  Finally, because each individual T-SQL statement does a lot of job, this greatly reduce the need to persist programming state through tables and or variables.

This is an introduction to stimulate your curiosity, I will add new blog entries about it soon, so stay tuned.