CodeSOD: The Apex of T-SQL

Thomas has some problems around the office. Specifically, the code he supports has all sorts of different ways to generate HTML. Rarely, it’s WebForms code living in as ASPX. Far more often, it’s hard-coded into the CodeBehind. Sometimes, it’s sitting in a resource file.

And then sometimes, it sits in a stored procedure like this:

ALTER Procedure [dbo].[proc_GetCurrentOrderStatus] @OrderId as Int,@MyVar varchar(2000),@LangId int,
        @Groupname varchar(10)
As                        
Declare @temp0 Table (                        
historyid int IDENTITY (1, 1) NOT NULL,                           
historytext nvarchar(1000) )                        


Declare @temp1 Table (                        
errid int IDENTITY (1, 1) NOT NULL,                           
errtext nvarchar(4000) )                        

Declare err_split Cursor for Select * from  dbo.split(@MyVar,',')                        
open err_split                        
declare @langText as varchar(2000)                        
  Fetch Next From err_split  into @langText                        
  While @@fetch_status=0                        
  Begin                        
          insert into @temp1 values(@langText)                        
   Fetch Next From err_split  into @langText                        
  End                         
close err_split                        
deallocate err_split

For those following along at home, this first block loads a series of language names from the clearly named @MyVar variable. With that cursor out of the way, we need to open a fresh one. It’s called History-cursor , and it’s populated from a basic SELECT statement. It’s after fetching the first record that things get interesting.

Fetch Next From History_cursor Into /* Snip */ @Lots, @Of, @Variables, @Here
set @tablestr = '<table border = "0" class="texbox" cellspacing= "0" cellpadding = "5">'                      
insert into @temp0 values(@tablestr)

Yes, that is hard-coded HTML dumped into a string variable and then dumped into a table variable. You’ll see a lot of that.

set @tablestr='' 
While @@FETCH_STATUS=0                         
Begin                        
 If @Groupname='Cust'                        
  Begin                        
   If @Status_Code = 'Cn'                        
   Begin                         
      set @tablestr = @tablestr + '<tr  class = "textbox" >'                
      set @tablestr = @tablestr + '<td nowrap ="nowrap" width = "250px" >Statut actuel de votre commande: </td>'                        
      set @tablestr = @tablestr + '<td nowrap ="nowrap" ><img src = ''' + @Status_Color 
        + ''' width = "15px" height="15px" alt="album" />&nbsp; ' + 
        (select errtext from @temp1 where errid=21) + ' </td>'                        
      set @tablestr = @tablestr + '</tr>'                        
      Break                         
   End

At this point, you might say, "Oh, I see how this works, this If repeats itself, almost identically, and the only variation is the errid used in the sub-query.

But no. Depending on the condition, you might see something like this:

If @Status_Code= 'Cn'                         
   Begin                        
      set @tablestr = @tablestr + '<tr class = "texbox" >'                        
      set @tablestr = @tablestr + '<td nowrap ="nowrap" width = "180px" >Statut actuel de votre commande:  </td>'                        
      set @tablestr = @tablestr + '<td nowrap ="nowrap" ><img src = ''' + @Status_Color 
        + ''' width = "15px" height="15px" alt="album" />&nbsp; ' + @status_name + '   ' + @Name + ' </td>'                        
      set @tablestr = @tablestr + '</tr>'                        
   End

Or perhaps, this:

If @VoucherID <> 0 And @Status_Code = 'N'                         
   Begin                        
      set @tablestr = @tablestr + '<tr class = "textbox">'                        
      set @tablestr = @tablestr + '<td nowrap ="nowrap" width = "180px" >Statut actuel de votre commande:  </td>'                        
      set @tablestr = @tablestr + '<td nowrap ="nowrap" ><img src = ''' + @Status_Color 
        + '''width = "15px" height="15px" alt="album" />&nbsp;' + @Status_Name + '  ' 
        + @OrderNumber + '    ' + @Name + '</td>'                        
      set @tablestr = @tablestr + '</tr>'                        
   End

So similar, yet so different. This block is hundreds of lines long, and each condition does something subtly different from each other block.

Someone read that doing too much concatenation must be bad, so at the bottom of the loop, we have this simple step:

insert into @temp0 values(@tablestr)                        
set @tablestr=''

After the loop closes and the cursors are deallocated, the rancid icing on this SQL-HTML cake:

set @tablestr = @tablestr + '</table>'                        
insert into @temp0 values(@tablestr)                        
set @tablestr=''                        
Select * from @temp0 order by historyid

At least we can take solace in the fact that this is a small product from Thomas’s company. No one would build a large-scale product completely dedicated to generating HTML from database stored procedures, right?

[Advertisement] Make your team a DevOps team with BuildMaster . Pairing an easy-to-use web UI with a free base platform, BuildMaster gets you started in minutes. See how Allrecipes.com and others use BuildMaster to automate their software delivery.

via The Daily WTF http://thedailywtf.com/Articles/The-Apex-of-TSQL.aspx