SQLTeam.com | Weblogs | Forums

SQL 2016 HTML DBmail table with multiple queries


Having trouble getting my syntax just right - a little help appreciated. If you scroll to the bottom and note the commented area, I can't seem to get that correct so that it will run.
I've tried a bunch of stuff, the latest being to just create a temp table and store the data in the temp table and then reference that (see just below), but I've got some syntax slightly off.

insert into #myPBL (id, pList)
select rs.id, rs.plist from (select distinct id, stuff((select '~' + code+ ' ' + upper(left(text,1)) + substring(lower(text),2,len(text)-1) from my table with (nolock) 
where (did=24) and (id=#C1.id) for xml path('')),1,1,'')plist from #C1 with (nolock))rs;

set @tableHTML = @tableHTML + 
   N'<table border="1" cellspacing="1" cellpadding="2" style="font-size: 10px;">' + blah blah header
</tr>' +  
   select (
td=d2.bp, '', 
td=d2.[weight], '',
td=d2.next_appt, ''

               for xml path('tr'), type),''

               ,(select '3' as [td/@colspan],td=comments, '' for xml path('tr'), type)

                    from #D1 with (nolock) where #D1.imredem=@imredem      

                --,(select '3' as [td/@colspan],td=#myPBL.pList, '' for xml path('tr'), type)

                  --   from #myPBL where #myPBL.imredem=@imredem

            for xml path('')) AS nvarchar(max)) + '</table>';


Please provide ddl, data and results. We can't make anything of this. What are you expecting the results to be?


Mike, I'm just asking for the correct syntax in order to append the line of

,(select '3' as [td/@colspan],td=#myPBL.pList, '' for xml path('tr'), type) from #myPBL where #myPBL.imredem=@imredem

without blowing up. Everything runs fine until I try to add that piece into the code. I've got some syntax slightly off.