Solved

Using cat with sql variable

Posted on 2012-04-10
8
251 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

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 34

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 34

Accepted Solution

by:
James0628 earned 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

792 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