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?