I had to fuss with this for a while until i got it. I needed dynamic sql in order to count rows in different tables. The tables depended on the dataset, and even the databases, so i had to be able to pass in a list of the names of the tables when the usp (user stored procedure) ran.
But i wanted to get the value of the stored procedure, and of course it usually returns 0 or 1 for success/failure. By using sp_executesql, we can configure a return or output variable and make sure our data, in this case the count, is returned to it. Then i put it into another local variable and use that for SQLMail.
set @SQL = 'select @mycount = count(*)
from database.dbo.' + @destTable + ''
EXEC sp_executesql @SQL
, N'@mycount nvarchar(10) OUTPUT'
,@mycount = @mycount OUTPUT
set @bodyText += CHAR(13) + CHAR(10) +
' There are ' + @mycount +
' rows in ' + @sourceTable + CHAR(13) + CHAR(10)
In the real case, there are pairs of table lookups and the CHAR(13)+CHAR(10) are needed to force a line feed for the email message in @body. Of course, you need to declare @bodyText first. AND FYI, you need to initialize it with, like, ‘report’. If you try to do a += into @bodyText before there is something in it, it will fail (and output null). Obvious answer is to use @bodyText first as an set =, and next as set +=, but this works too. I did not test with ‘ ‘ (with a space) and it would have worked with ‘.’ and then i could remove it later, but this works for me.
