none
Need help with dynamic SQL

    Question

  • Hello,

    I'm using following to generate the HTML output. But the tw0 columns @Previous & @Current are passed as variables. I'm not seeing any errors except the (td) values in the variable columns(@Previous, @Current) are static (as column names). How can i get the actual values of these fields ...

    --@Previous ='[06/24/2013]'
    --@Current='[06/25/2013]'
    
    DECLARE @tableHTML NVARCHAR(MAX) ;
        SET @tableHTML =
        N'<html><body><h1>Report</h1>' +
        N'<table border="1" width="100%">' +
        N'<tr bgcolor="gray"><td>ServerName</td><td>DBName</td><td>'+@Previous+'</td><td>'+@Current+'</td><td>[(%)Change]</td></tr>'+
          CAST((
            SELECT
                        td = ServerName, '',
                        td = DBName, '',
                        td = @Previous, '',
                        td = @Current, '',
    					'td/@bgcolor'=CASE WHEN CAST([PercentageChange(%)] AS DECIMAL(10,2))<10 THEN 'Green'
    								  ELSE 'Red' END,
                        td = [PercentageChange(%)], ''
                FROM #DBSize_Report
                        FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'


    • Edited by dvsrk Tuesday, June 25, 2013 10:47 PM
    Tuesday, June 25, 2013 10:30 PM

Answers

  • I have figured it out with only one possible approach that I'm aware of as below,

    DECLARE @query NVARCHAR(MAX)
    DECLARE @html NVARCHAR(MAX)
    DECLARE @Previous DATETIME
    SET @Previous='[06/24/2013]'
    DECLARE @Current DATETIME 
    SET @Current='[06/25/2013]'
    
    Set @query = N'SET @html=
    				CAST((SELECT
    					 td = ServerName, '''',
                 
                        td = DBName, '''',
    
                        td = '+@Previous+', '''',
    				
                        td = '+@Current+', '''',
                        
    					''td/@bgcolor''=CASE WHEN CAST([Change(%)] AS DECIMAL(10,2))<10 THEN ''#BCF5A9''
    								  ELSE ''#FE2E2E'' END,
                        td = [Change(%)], ''''
                FROM #Report
                        FOR XML PATH(''tr''), TYPE) AS NVARCHAR(MAX))'
                        
    EXECUTE SP_EXECUTESQL @query,N'@html NVARCHAR(MAX) OUTPUT', @html OUTPUT 
    Get the output and concatenate table header :)


    Wednesday, June 26, 2013 5:16 PM

All replies

  • Adding sample output

    Mail Header

    ServerName DBName [06/24/2013] [06/25/2013] [PercentageChange(%)]
    DB01 DB [06/24/2013] [06/25/2013] 0.00
    DBServer01 DB01 [06/24/2013] [06/25/2013] 0.00
    DBServer01 DB02 [06/24/2013] [06/25/2013] 0.00
    DBServer02 DB01 [06/24/2013] [06/25/2013]
    Tuesday, June 25, 2013 10:34 PM
  • Hello,

    I'm using following to generate the HTML output. But the tw0 columns @Previous & @Current are passed as variables. I'm not seeing any errors except the (td) values in the variable columns(@Previous, @Current) are static (as column names). How can i get the actual values of these fields ...


    Your code should be something like below:

    DECLARE @PREVIOUS DATETIME
    SET @PREVIOUS='[06/24/2013]'
    DECLARE @CURRENT DATETIME 
    SET @CURRENT='[06/25/2013]'
    
    DECLARE @TABLEHTML NVARCHAR(MAX) ;
        SET @TABLEHTML =
        N'<HTML><BODY><H1>REPORT</H1>' +
        N'<TABLE BORDER="1" WIDTH="100%">' +
        N'<TR BGCOLOR="GRAY"><TD>SERVERNAME</TD><TD>DBNAME</TD><TD>'+@PREVIOUS+'</TD><TD>'+@CURRENT+'</TD><TD>[(%)CHANGE]</TD></TR>'+
          CAST((
            SELECT
                        TD = SERVERNAME, '',
                        TD = DBNAME, '',
                        TD = @PREVIOUS, '',
                        TD = @CURRENT, '',
    					'TD/@BGCOLOR'=CASE WHEN CAST([PERCENTAGECHANGE(%)] AS DECIMAL(10,2))<10 THEN 'GREEN'
    								  ELSE 'RED' END,
                        TD = [PERCENTAGECHANGE(%)], ''
                FROM #DBSIZE_REPORT
                        FOR XML PATH('TR'), TYPE) AS NVARCHAR(MAX)) + N'</TABLE></BODY></HTML>'
    
    SELECT @TABLEHTML

    NOTE: Not Tested

    Thanks.


    bala krishna

    Wednesday, June 26, 2013 4:57 AM
  • For better understanding, try with below code:

    DECLARE @STR VARCHAR(100)       -- we should declare the variable data type
    SET @STR='WELCOME TO INDIA'    -- assign some value
    SELECT @STR AS VAL        -- Print the value

    Thanks.


    bala krishna

    Wednesday, June 26, 2013 5:01 AM
  • Hello,

    I'm using following to generate the HTML output. But the tw0 columns @Previous & @Current are passed as variables. I'm not seeing any errors except the (td) values in the variable columns(@Previous, @Current) are static (as column names). How can i get the actual values of these fields ...


    Your code should be something like below:

    DECLARE @PREVIOUS DATETIME
    SET @PREVIOUS='[06/24/2013]'
    DECLARE @CURRENT DATETIME 
    SET @CURRENT='[06/25/2013]'
    
    DECLARE @TABLEHTML NVARCHAR(MAX) ;
        SET @TABLEHTML =
        N'<HTML><BODY><H1>REPORT</H1>' +
        N'<TABLE BORDER="1" WIDTH="100%">' +
        N'<TR BGCOLOR="GRAY"><TD>SERVERNAME</TD><TD>DBNAME</TD><TD>'+@PREVIOUS+'</TD><TD>'+@CURRENT+'</TD><TD>[(%)CHANGE]</TD></TR>'+
          CAST((
            SELECT
                        TD = SERVERNAME, '',
                        TD = DBNAME, '',
                        TD = @PREVIOUS, '',
                        TD = @CURRENT, '',
    					'TD/@BGCOLOR'=CASE WHEN CAST([PERCENTAGECHANGE(%)] AS DECIMAL(10,2))<10 THEN 'GREEN'
    								  ELSE 'RED' END,
                        TD = [PERCENTAGECHANGE(%)], ''
                FROM #DBSIZE_REPORT
                        FOR XML PATH('TR'), TYPE) AS NVARCHAR(MAX)) + N'</TABLE></BODY></HTML>'
    
    SELECT @TABLEHTML

    NOTE: Not Tested

    Thanks.


    bala krishna

    Thanks.

    If i'm right the only change you have made was SET for variables @Previous and @Current. These are assigned dynamically and the SET that i added in my code as comment is just for understanding what I'm passing in those variables.


    • Edited by dvsrk Wednesday, June 26, 2013 5:17 PM updated
    Wednesday, June 26, 2013 5:11 PM
  • I have figured it out with only one possible approach that I'm aware of as below,

    DECLARE @query NVARCHAR(MAX)
    DECLARE @html NVARCHAR(MAX)
    DECLARE @Previous DATETIME
    SET @Previous='[06/24/2013]'
    DECLARE @Current DATETIME 
    SET @Current='[06/25/2013]'
    
    Set @query = N'SET @html=
    				CAST((SELECT
    					 td = ServerName, '''',
                 
                        td = DBName, '''',
    
                        td = '+@Previous+', '''',
    				
                        td = '+@Current+', '''',
                        
    					''td/@bgcolor''=CASE WHEN CAST([Change(%)] AS DECIMAL(10,2))<10 THEN ''#BCF5A9''
    								  ELSE ''#FE2E2E'' END,
                        td = [Change(%)], ''''
                FROM #Report
                        FOR XML PATH(''tr''), TYPE) AS NVARCHAR(MAX))'
                        
    EXECUTE SP_EXECUTESQL @query,N'@html NVARCHAR(MAX) OUTPUT', @html OUTPUT 
    Get the output and concatenate table header :)


    Wednesday, June 26, 2013 5:16 PM