execute execute

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.

Advertisements