0

I am searching a query which generate html table. I found this query, but I can not understand what is use of such so many single quote used here and why all code create in variable. I am new in sql please help me.

BEGIN
    SET NOCOUNT ON;

    IF @orderBy IS NULL
    BEGIN
        SET @orderBy = ''
    END

    SET @orderBy = REPLACE(@orderBy, '''', '''''');
    DECLARE @realQuery NVARCHAR(MAX) = ' //this is variable which take all query as string
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub; -- how this temp table create 

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
    --in coalesce why so many '''' used
    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
';

    EXEC sys.sp_executesql @realQuery
    ,N'@html nvarchar(MAX) OUTPUT'
    ,@html = @html OUTPUT
END

Above query is working fine, but I can't understand it.

Devraj Gadhavi
  • 3,541
  • 3
  • 38
  • 67
suhas
  • 39
  • 7
  • 1
    The doubled qoutes are needed because they are **within** a string. This code is dynamically creating a statement which is executed at the end. All qoutes you'd normally write in your statements must be doubled. Otherwise they would be taken as *end-of-string*... But you might want to [read this answer](http://stackoverflow.com/a/39487565/5089204). I think this is a much easier approach to create a html table from a `SELECT`... – Shnugo Sep 27 '16 at 09:20
  • 1
    if you want to specify that a string literal contains a single quote, then you have to 'escape' the character as two single quotes next to each other (the c language would do this with \'). It looks really clumsy! I f you want to assign the string '' to something, you can end up with things like SET @X = ''''''; for example – Cato Sep 27 '16 at 10:21
  • 1
    Dynamic SQL that calls dynamic SQL. Lots and lots of escaped and re-escaped single quotes. – Gordon Linoff Sep 27 '16 at 11:02
  • 1
    Before EXEC add a line with **print @realQuery** and you'll see what is doing. Also check that page https://msdn.microsoft.com/en-us/library/ms188001.aspx – derloopkat Sep 27 '16 at 11:38

0 Answers0