Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using cat with sql variable

Posted on 2012-04-10
8
Medium Priority
?
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 12

Expert Comment

by:Anuradha Goli
ID: 37827525
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
ID: 37827631
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
ID: 37832670
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 37846719
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 35

Expert Comment

by:James0628
ID: 37854483
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
ID: 37855823
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 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 37859236
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 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