none
Sending the Query results in HTML format using DBmail

    Question

  • Hello,

    I'm trying to send the query results via DB mail where the last 3 cloumns of that specific table are formed dynamically,

    If i give the static columns in the query it is working perfectly but when i assign the clolumn names using a variable is is not working. 

    Could someone correct.

    /*WorkinQuery where static columns names are set (DATE VALUES):*/
    
    DECLARE @tableHTML NVARCHAR(MAX) ; 
        SET @tableHTML = 
        N'<html><body><h><U>Outbound:</U></h1><br>' + 
        N'<table border="1" width="100%">' + 
        N'<tr bgcolor="gray" style="color:white">
    	<td width="10%">State_CD</td>
    	<td width="10%">Company_CD</td>
    	<td width="10%">Extract_Type</td>'
            +@var1+'
    	<td width="10%">Weekly_Total</td>
         </tr>' + 
                CAST(( 
            SELECT 
                        td = State_CD, '',
                        td = Company_CD, '',
                        td = Extract_Type, '',
    
                        td = [05/11/2013], '', 
                        td = [05/13/2013], '', 
                        td = [05/14/2013], '', 
    
                        td = Weekly_Total, '' 
                FROM dbo.Report_Inbound
                        FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'
    
    
    
    
    /*In the below query I'm passing the column names(@var2) by variable which is not working:*/
    
    
    
    
    SET @var2='td = [05/11/2013], '', td = [05/13/2013], '', td = [05/14/2013], '''
    DECLARE @tableHTML NVARCHAR(MAX) ; 
        SET @tableHTML = 
        N'<html><body><h><U>Outbound:</U></h1><br>' + 
        N'<table border="1" width="100%">' + 
        N'<tr bgcolor="gray" style="color:white">
    	<td width="10%">State_CD</td>
    	<td width="10%">Company_CD</td>
    	<td width="10%">Extract_Type</td>'
            +@var1+'
    	<td width="10%">Weekly_Total</td>
    		</tr>' + 
                CAST(( 
            SELECT 
                        td = State_CD, '',
                        td = Company_CD, '',
                        td = Extract_Type, '',
    
                        +@var2
    
                        ,td = Weekly_Total, '' 
                FROM dbo.Report_Inbound
                        FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'
    
    
    
    
    
    
    
    
    
    Thank you.
    Sunday, May 19, 2013 8:52 PM

All replies

  • I guess that this dbo.Report_Inbound is related to the other pivot thread you have. But what is the exact definition of Report_inbound? If it is the result of the pivot query you had, why not just make the column name static like {Day1}, {Day2}, {Day3}, and then run replace() on the final HTML string to get actual dates into it?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 19, 2013 9:05 PM
  • Yes, It is the result of the pivot query from my previous thread and I agree the approach you have suggested is doable. 

    But just to understand Is it possible to have variables in the SELECT of above mentioned HTML and why it is giving me two different results ? Am i doing any mistake here ?

    Thank you

    Monday, May 20, 2013 12:15 AM
  • You have the query:

     SELECT td = State_CD, '',
            td = Company_CD, '',
            td = Extract_Type, '',
            +@var2
            ,td = Weekly_Total, ''
    FROM dbo.Report_Inbound

    This is legal syntax, but it would only work if @var2 is a number, or something that can be converted to a number. You had @var2 to as a string, and I supposed you got a conversion error. (You should always include any error message you get.)

    If you intended that SQL Server would inject the SQL code you had in the variable, and then at run-time reparse the query, you are mistaken.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 20, 2013 7:46 AM