johnnyg123
asked on
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>'
	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';
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
		from db3.audit.dbo.DeptAudit
	 where department = 'Accounting' and NTW = ' Y'
		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
		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 ( (@@STRtest) AS NVARCHAR(MAX) ) + '</table>' but the output is not being formatted into a table
Not sure what I am doing wrong
	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>'
	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';
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
		from db3.audit.dbo.DeptAudit
	 where department = 'Accounting' and NTW = ' Y'
		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
		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 ( (@@STRtest) AS NVARCHAR(MAX) ) + '</table>' but the output is not being formatted into a table
Not sure what I am doing wrong
ASKER
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
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
ASKER
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!!!!
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.
This is driving me nuts....please help!!!!
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?
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
Or maybe I'm completely off base. Like I said, I haven't tried to do this kind of thing before.
James
ASKER
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</t h><th>SQL< /th><th>DB </th><th>A PP</th></t r>' +
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</t h><th>SQL< /th><th>DB </th><th>A PP</th></t r>' +
+ 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
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
N'<th>NTW</th><th>PM</th><
N'<th>CCT</th><th>FLR</th>
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.
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
N'<th>NTW</th><th>PM</th><
N'<th>CCT</th><th>FLR</th>
+ 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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';