Solved

Using cat with sql variable

Posted on 2012-04-10
8
244 Views
Last Modified: 2012-05-25
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
Comment
Question by:johnnyg123
8 Comments
 
LVL 12

Expert Comment

by:Anuradha Goli
Comment Utility
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
 

Author Comment

by:johnnyg123
Comment Utility
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
 

Author Comment

by:johnnyg123
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 

Author Comment

by:johnnyg123
Comment Utility
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now