Using cat with sql variable

I am using the following to send query output formated in an html table to an email address


	SET @@tableHTML =
	@@StrEmailIntroLine
	+ @@StrHTMLTableDef
	+ @@StrHTMLColumnHeaders
	 +   CAST ( ( select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
		from db3.audit.dbo.DeptAudit
	    where department = 'Accounting' and NTW = ' Y'

		order by department, title, fullname
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +  '</table>'

&#9;EXEC MSDB.dbo.sp_send_dbmail
&#9;@Profile_Name = 'DB3',
&#9;@Recipients = 'me@me.com',
&#9;@Subject = 'Accounting Department Users and Application Access',
&#9;@body = @@tableHTML,
&#9;@body_format = 'HTML';


This works fine

I am try to parameritize and would like to put the select statement in a global variable but can't get it to work

I set @@STRtest to

select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
&#9;&#9;from db3.audit.dbo.DeptAudit
&#9;    where department = 'Accounting' and NTW = ' Y'

&#9;&#9;order by department, title, fullname


and then replaced

 +   CAST ( ( select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
&#9;&#9;from db3.audit.dbo.DeptAudit
&#9;    where department = 'Accounting' and NTW = ' Y'

&#9;&#9;order by department, title, fullname
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +  '</table>'




with

 + CAST ( (@@STRtest) AS NVARCHAR(MAX) ) +  '</table>' but the output is not being formatted into a table

Not sure what I am doing wrong
johnnyg123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anuradha GoliSystems Development / Support SpecialistCommented:
set @@STRtest= CAST ( ( select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
            from db3.audit.dbo.DeptAudit
          where department = 'Accounting' and NTW = ' Y'
            order by department, title, fullname
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +  '</table>'

SET @@tableHTML =
      @@StrEmailIntroLine
      + @@StrHTMLTableDef
      + @@StrHTMLColumnHeaders
       +   @@STRtest

      EXEC MSDB.dbo.sp_send_dbmail
      @Profile_Name = 'DB3',
      @Recipients = 'me@me.com',
      @Subject = 'Accounting Department Users and Application Access',
      @body = @@tableHTML,
      @body_format = 'HTML';
johnnyg123Author Commented:
Thanks for the response

The issue I'm having is that I am trying to build the query dynamically so I want the query to be in a sql variable so  I need


set @@STRtest= CAST ( ( select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
            from db3.audit.dbo.DeptAudit
          where department = 'Accounting' and NTW = ' Y'
            order by department, title, fullname
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +  '</table>'

to be something like

set @@STRtest= CAST ( ( @@strsql              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +  '</table>'

but can't seem to get that to work
johnnyg123Author Commented:
for clarification I replaced   +   CAST ( ( select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
            from db3.audit.dbo.DeptAudit
          where department = 'Accounting' and NTW = ' Y'

            order by department, title, fullname
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +  '</table>'

with


+ Cast((@@STRtest123) AS NVARCHAR(MAX)) + '</table>'



I did a select on @@strtest123 and it contains

   select td = FullName,'',td = Title,'',td = NTW,'',td = PM,'',td = WZD,'',td = CCT,'',td = FLR,'',td = EZP,'',td = [sql],'',td = DB,'',td = app from gvcdb3.acresuseraudit.dbo.DeptAudit where department = 'Accounting' and NTW = ' Y' order by title, fullname FOR XML PATH ('tr'), TYPE)

This is driving me nuts....please help!!!!
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ZberteocCommented:
Is @@STRtest123 a SQL global variable? It is confusing. Ca you explain in words what you are trying to do and what language do you use?
James0628Commented:
I have not tried to use HTML formatting in SQL before, but it looks to me like your original query is running the ( select td = FullName, '', td = Title, '', ... FOR XML PATH('tr'), TYPE ) query and then CAST'ing the output from that as nvarchar.  But when you use the variable, you're CAST'ing the variable (the query string, not the results) as nvarchar.  If I'm right, maybe you need to execute the query string (@@STRtest) and then CAST the output from that to nvarchar?

 Or maybe I'm completely off base.  Like I said, I haven't tried to do this kind of thing before.

 James
johnnyg123Author Commented:
Thanks so much for the additional replies

Zberteoc,

yes the variable is defined



James

I think you may be on the right track but not sure how to do what you are suggesting


All I want to do is allow the query itself to be dynamic (in a variable) and not hard coded


If the stored proc contains the following, the output is formatted to an html table


DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

     N'Please review your department users and report any discrepancies to IT. <br> <br>  
      The code descriptions are as follows:  <br><br>
      NTW - Network User, <br>
      PM - Patron Management, <br>
      MA - Machine Accounting,<br>
      CTA - CTA, <br>
      FLR - Floorstaff,  <br>
      EZP - EZ Pay, <br>
      SQL - GP SQL Login, <br>
      DB - GP Database Login, <br>
      APP - GP Application User. <br> <br>Thank you.' +

    N'<H1>Accounting Department Users and Application Access</H1>' +
    N'<table border="1">' +
    N'<tr><th>FullName</th><th>Title</th>' +
    N'<th>NTW</th><th>PM</th><th>WZD</th>' +
    N'<th>CCT</th><th>FLR</th><th>EZP</th><th>SQL</th><th>DB</th><th>APP</th></tr>' +
    CAST ( ( select
                    td = FullName,       '',
                    td = Title, '',
                    td = NTW, '',
                    td = PM, '',
                    td = WZD, '',
                    td = CCT, '',
                    td = FLR, '',
                    td = EZP, '',
                    td = sql, '',
                    td = DB, '',
                    td = app
 
            from gvcdb3.acresuseraudit.dbo.DeptAudit
      where NTW = ' Y '

            order by department, title, fullname
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='jghar@gv.com',
@Profile_Name = 'DB3',      
    @subject = 'Accounting Department Users and Application Access',
    @body = @tableHTML,
    @body_format = 'HTML' ;

                                                


However,  if the stored proc contains the following I got the column headers but not the table rows

DECLARE @tableHTML  NVARCHAR(MAX) ;

DECLARE @strSQL  NVARCHAR(2000) ;

DECLARE @strSQLHTML  NVARCHAR(2000)

set @strsql = 'select
                    td = FullName,       '''',
                    td = Title, '''',
                    td = NTW, '''',
                    td = PM, '''',
                    td = WZD, '''',
                    td = CCT, '''',
                    td = FLR, '''',
                    td = EZP, '''',
                    td = sql, '''',
                    td = DB, '''',
                    td = app
 
from db3.audit.dbo.DeptAudit
  where department = ''Accounting'' and NTW = '' Y''

order by department, title, fullname FOR XML PATH(''tr''), TYPE )'



SET @tableHTML =

     N'Please review your department users and report any discrepancies to IT. <br> <br>  
      The code descriptions are as follows:  <br><br>
      NTW - Network User, <br>
      PM - Patron Management, <br>
      MA - Machine Accounting,<br>
      CTA - CTA, <br>
      FLR - Floorstaff,  <br>
      EZP - EZ Pay, <br>
      SQL - GP SQL Login, <br>
      DB - GP Database Login, <br>
      APP - GP Application User. <br> <br>Thank you.' +

    N'<H1>Accounting Department Users and Application Access</H1>' +
    N'<table border="1">' +
    N'<tr><th>FullName</th><th>Title</th>' +
    N'<th>NTW</th><th>PM</th><th>WZD</th>' +
    N'<th>CCT</th><th>FLR</th><th>EZP</th><th>SQL</th><th>DB</th><th>APP</th></tr>' +
   
   
   
    + CAST((@strsql) AS NVARCHAR(MAX) ) + ' </table>'


   
   
   

EXEC msdb.dbo.sp_send_dbmail @recipients='jghar@gv.com',
@Profile_Name = 'DB3',      
    @subject = 'Accounting Department Users and Application Access',
    @body = @tableHTML,
    @body_format = 'HTML' ;

I have put the output in the attached file
html.doc
James0628Commented:
When you use CAST (( select td = FullName, '', td = Title, '', ... FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ), the SELECT is executed and the output from that is converted to nvarchar.  But when you use CAST((@strsql) AS NVARCHAR(MAX) ), you're simply converting the string in @strsql to nvarchar.  You're not actually executing the sub-query.  So you just end up with that string - 'select td = FullName, '''', <etc.>' - dumped into your data, as shown in the html.doc file that you posted.

 As I said before, I haven't used HTML in SQL before, and I haven't really used dynamic SQL either (I've never needed it), but in theory, you just need to execute the SQL in the variable.  Change your CAST from

CAST((@strsql) AS NVARCHAR(MAX) )

   to

CAST((EXEC (@strsql)) AS NVARCHAR(MAX) )

   or

CAST((EXEC sp_executesql @strsql) AS NVARCHAR(MAX) )


 Hopefully that will do it.

 I don't know if it will make any difference if you just use EXEC, or use EXEC sp_executesql.  In theory, either should work, but they work a bit differently, so one may be better than the other for your purposes.  You could try to read up on them and see if either seems to offer any specific benefits in this situation.  sp_executesql does allow parameters, which might be useful if you're trying to change that query on the fly.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.