Return to Question

Replaced code images with code text.

Source Link

edited Mar 25, 2022 at 13:15

   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

   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)
                         )

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.

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, etc.) that my general feeling is that building a complex application in TSQL is a very perilous business, no matter how clever you are.

   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

   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)
                         )

Source Link

asked Mar 25, 2022 at 13:02

Joshua Frank

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

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:

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:

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, 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?