How to write robust or alternative to TSQL data processing routines in SqlServer?

Question

My company has a workflow that involves ingesting huge amounts of raw data from external sources into SqlServer and then interpreting and weaving that data into our own application tables. This weaving process consists of many thousands of lines of TSQL code in stored procedures, which call other stored procedures, all of which look more or less like this:

   IF (@XXXId = -1)           -- No Record Found, Insert
               BEGIN
                  IF (@TransactionId IS NOT NULL)
                     BEGIN
                        SET @programBegins = GETDATE ()
                        SET @programEnds = dateadd (day, 90, @programBegins)
                     END

                  --Create/Update User Login & Contact Record if Needed
                  INSERT INTO @outputTable
                  EXEC CreateXXX @loginId,
                       @bypassUserCreation,
                       @className,
                       @sectionNumber,
                       @grade,
                       @year,
                       @isImport,
                       @programBegins,
                       @programEnds,
                       @emailInfo,
                       @excludeRankings,
                       @teamVendorId,
                       @VendorType,
                       @isPlaceHolder,
                       @docEntry,
                       @docNum,
                       @PONumber,
                       @BPCode,
                       @XXXTeamId OUTPUT

                  DECLARE @errorText AS NVARCHAR (100)
                  SET @errorText =
                           'An error occured Pushing record: '
                         + cast (@teamVendorId AS NVARCHAR (50))

                  IF @@error <> 0
                     BEGIN
                        RAISERROR (@errorText, 1, 1)
                        RETURN
                     END
               END

Now, I understand that you can write good or bad code in any language, and I understand that some of the differences between TSQL and, say, c# are stylistic, not functional, but it still seems to me that TSQL makes it especially hard to write good code, and in many ways forces you to write terrible code.

The examples are many, from the small (no enums, for instance) to the IMO rather devastating, such as no complex types or ability to reuse basic things. For instance, if you want to group information, you need a table valued variable:

   DECLARE @XXX TABLE
                         (
                            [rowNum]              [int],
                            [RowId]               [int],
                            [Id]                  [nvarchar] (50),
                            [XXXId]            [nvarchar] (50),
                            [XXXId]           [nvarchar] (50),
                            [LocalId]             [nvarchar] (50),
                            [Name]                [nvarchar] (100),
                            [Grade]               [int],
                            [RowState]            [tinyint],
                            [LastModified]        [datetime],
                            [YYYId]         [int],
                            [YYYName]       [nvarchar] (50),
                            [State]            [nvarchar] (30),
                            [Email]               [nvarchar] (100),
                            [UserId]              [nvarchar] (100),
                            [Password]            [nvarchar] (25)
                         )

and if you need this in another proc, you have to declare this again in that proc.

I want to disclaim that I am not a TSQL expert, and maybe there's a workaround for this particular issue. But these procs were written by someone who is, and there are so many issues like this (with intellisense, compile time type checking, debugging experience, refactoring tools, built in operators, things you can't do in a UDF, no function chaining syntax, insane parenthesis usage, insane CAST requirements, etc.) that my general feeling is that building a complex application in TSQL is a very perilous business, no matter how clever you are.

Partially as a result of this, our import processes are becoming unmaintainable. The business logic is complex, but I think the TSQL language is making it even more complex.

And yet, I'm not sure what to do instead. Get better at TSQL? Use SqlClr? Use C#? Is there an alternative to TSQL that runs in the db?

How do people handle this kind of situation?

Answer 1

Your specific table variable example isn't literally true since table types exist (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql). So if you have an exact same structure to reuse in different stored procedures, you can add its definition to the database and then use that type in multiple places:

-- constraints and IDENTITY added just to show they can be
CREATE TYPE NameAmountTable AS TABLE (
    RowNum INT IDENTITY PRIMARY KEY,
    Name VARCHAR(35) NOT NULL UNIQUE,
    Amount DECIMAL(12,2)
);

GO

CREATE OR ALTER PROCEDURE Proc1 AS
BEGIN
    DECLARE @NameAmountTable NameAmountTable;
    INSERT INTO @NameAmountTable
        (Name, Amount)
    VALUES
        ('Example 1', 12345.67),
        ('Example 2', 12345.67);
    
    SELECT * FROM @NameAmountTable;
END;

GO

CREATE OR ALTER PROCEDURE Proc2 AS
BEGIN
    DECLARE @NameAmountTable NameAmountTable;
    INSERT INTO @NameAmountTable
        (Name, Amount)
    VALUES
        ('Example 3', 12345.67),
        ('Example 4', 12345.67);
    
    SELECT * FROM @NameAmountTable;
END;

That said, table types aren't the equivalent of classes in C#, and are barely the equivalent of structs. In fact one of their main purposes is interop with application code: declare a stored procedure parameter as a table type, and it enables passing data in bulk from (say) C# in a strongly-typed fashion, 'strongly typed' in both a general and database-y sense (hence the ability to include most sorts of constraints in a table type's definition). Try to use table types more broadly however, and you will quickly come up against their limitations - hard to change once something else is using the type in its own signature, impossible to nest, no encapsulation or inheritance, etc. So you're right, T-SQL is an absolute disaster when it comes to even a 1990s OOP paradigm, let alone anything more modern.

However, the usage cases of procedural SQL (whether that's T-SQL or PL/pgSQL or whatever) are typically fairly boring, repetitive bulk data preparation and manipulation tasks, like your wider example actually illustrates. Essentially the work is about glueing together a sequence of INSERT, UPDATE or DELETE statements (= pure database SQL things) with a bit of conditional logic, not too much though, with the ability to raise a custom error when needed.

Now, the overall sequence of those INSERT, UPDATE and DELETE statements will likely be important to get right; it will likely require knowledge of the underlying database design (e.g. knowledge of foreign keys, check constraints and other database-y things). Moreover, for performance, the best implementation will probably need to consider topics such as temporary tables, indexes and so forth (so more database-y things). What you need, then, is a language that enables working with the database directly, making pure SQL calls trivial, and includes just enough general programming features to tie things altogether. And that, in a nutshell, is pretty much what T-SQL provides, ditto the equivalent procedural SQL extension languages of other RDBMSs.

As to how you write 'robust' T-SQL data processing routines: really that just comes down to common sense. Source control SQL object definitions as seriously as you source control pure application code, and where it makes sense add unit tests - realistically test-driven development of T-SQL sprocs is impractical (and a bit pointless, cf. the 'glue' point above), but that doesn't mean you shouldn't have any sort of regression testing in place, it will probably just be more end-to-end in nature compared to unit testing of the C# code. Also don't start using fancier T-SQL features just because you can, for example exception handling is better done on the C# side, even though T-SQL has a TRY/CATCH construct.

Answer 2

It's worth noting firstly that SQL is fundamentally a very old language - perhaps only exceeded by COBOL in terms of both age and ubiquity - so there are certainly shortfalls and disharmonies amongst the language.

It's worth noting secondly that SQL was not designed for ease of expressing very complicated hand-crafted algorithms, which has always been the realm of other languages.

All that said, despite the perceived deficiencies of the language, I haven't really found something else that is more productive for data processing work.

Repeating those table declarations will be the least of your worries when you try and move all the processing to application code, using plain files as inputs and outputs, or something like that.