SQLTeam.com | Weblogs | Forums

SQL 2016 HTML DBmail table with multiple queries


#1

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>' +  
   cast((  
   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>';

#2

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


#3

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.