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" /> ' +
(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" /> ' + @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" /> ' + @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
