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?