[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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
0
johnnyg123
Asked:
johnnyg123
1 Solution
 
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';
0
 
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
0
 
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!!!!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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?
0
 
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
0
 
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
0
 
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
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now