0

I am working on tsql to create an html report. The existing table is a list of error_codes organized as follows:

to clarify what is this table doing, it is just listing error codes and description for that error... Some errors are headers and some are not! The header entries marked with 1

| error_code | error_disc                  | error_seq | isHeader |
 -----------------------------------------------------------------
|    XYT3    | Description for this error  | 100       |    1     |
 -----------------------------------------------------------------
|    ZTY5    | Another description         | 101       |    1     |
 -----------------------------------------------------------------
|    UJ0B1   | A child error entry         | 102       |    0     |
 -----------------------------------------------------------------
|    XXCV    | Another header              | 103       |    1     |
 -----------------------------------------------------------------
|    GTER4   | Second child entry          | 104       |    0     |
 -----------------------------------------------------------------

I need to display that table in HTML format and make every header with its child error entries in a separate <tr>

I used the following code:

declare @listErrorMsgs varchar(max) = (
SELECT  
    CASE 
        WHEN isHeader = 1 THEN 'vertical-align: text-top; border-top: 1px solid #2c3e50; font-weight: bold;' 
        ELSE 'vertical-align: text-top;' END as [td/@style] , error_disc as td, '',
    CASE 
        WHEN isHeader = 1 THEN 'vertical-align: text-top; border-top: 1px solid #2c3e50; font-weight: bold;'
        ELSE 'vertical-align: text-top;' END as [td/@style] , error_code as td, ''

    FROM #Table
    ORDER BY error_seq
    FOR XML PATH('tr') 
)

The results were very good, table screenshot

However, for some reasons, the system doesn't allow css/styles with this code when importing the stored procedure.

Is there a way to have this query works without styles?

Thank you

Ahmed Ali
  • 127
  • 2
  • 13
  • This *really* shouldn't be handled on the data layer... – Siyual Dec 05 '16 at 14:52
  • Agree, but the issue is I am dealing with an existing system and I have no other option – Ahmed Ali Dec 05 '16 at 14:56
  • I posted an answer with code to create a HTML-table from any SELECT with CSS-support via class names here: http://stackoverflow.com/a/39487565/5089204 – Shnugo Dec 05 '16 at 15:13
  • @Siyual, why not? Where ever the database is the **direct** source for a reporting engine, I can see nothing wrong using the database to do things usually done in the presentation layer. Especially this need to create HTML table layout for a result set is quite generically solved. My linked answer will do this with one single call... – Shnugo Dec 05 '16 at 15:16
  • @AhmedAli, sorry, I did not read the *system doesn't allow css/styles with this code* carefully enough. You could think about *old-fashioned* text tables with a fixed width font like *courier new* and padding with blanks. Another way could be to deliver the resultset in a way, that combined entries are returned as one single concatenated string with an internal line break... – Shnugo Dec 05 '16 at 15:25
  • @Shnugo, no problem. It is a little bit strange to me. Unfortunately I am not able to change on the existing data/system. The only option is the stored procedure – Ahmed Ali Dec 05 '16 at 16:08
  • @AhmedAli, What is the actual problem: You fill in style information, but the displaying system doesn't allow/support this? Or do you have problems to enter the style information into your ``-tags? If you are allow to create a new function, you could use the function of my linked answer to create the table in hand in class names for header and table body... – Shnugo Dec 05 '16 at 16:16
  • @AhmedAli ...or you could modify my function the way, that it generates the header with `` instead of `` (was better anyway...) and use general styles for `` and `` – Shnugo Dec 05 '16 at 16:17
  • @AhmedAli I just edited my function in the linked answer. Using `` for table-heads will lead to a classical *caption-layout* implicitly. At least this is easier to tweak... – Shnugo Dec 05 '16 at 16:28
  • @Shnugo, thank you. Although it is not the solution to my issue, it helps a lot. Thanks again – Ahmed Ali Dec 05 '16 at 18:45

0 Answers0