1

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?

11
  • ome of the differences between TSQL and, say, c# are stylistic not really. SQL is not a programming language. T-SQL put some nuts and bolts on to provide te ability to program....somewhat. The differences between C# and T-SQL are large.
    – Pieter B
    Mar 25, 2022 at 13:09
  • @PhilipKendall Let me see about replacing the images with code. Mar 25, 2022 at 13:09
  • @PieterB I was being nice, I suppose, not wanting to offend people who do in fact program in TSQL all day. Maybe the essence of my question about why it's hard to program in TSQL is that it's not really a proper programming language. Mar 25, 2022 at 13:11
  • 1
    @PhilipKendall Done. Thanks for letting me know. Mar 25, 2022 at 13:15
  • 1
    @JoshuaFrank, it's very difficult to say in the abstract. SQL is designed for data processing, so if you are simply reading data in and transforming it, it would be surprising if another language is found to be more straightforward. There is always the possibility either that your process is absurdly complicated by its essential nature (due to radical mismatches in the source and target schemas), or that there is a lack of competence in using SQL (which is either creating spaghetti, or is creating adequate code but making it feel like extremely hard work).
    – Steve
    Mar 25, 2022 at 15:59

2 Answers 2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.