TSQL - Select Count(*) ..into Varchar variable

Question

let's say I have 10 tables and I need to write a simple LOG entry that states how many records each tables has.

Something like

Declare @msg Varchar(MAX)
Set @msg = 'Process Succeeded; Table1 has xx record, Table2 has zz records, Table3 has ww records ...'

Insert INTO LOG (msg) VALUES (@msg)

Answer 1

Or roll it up into:

 declare @Summary as VarChar(256)
 select @Summary =
   'Foo: ' + Cast( ( select Count(42) from Foo ) as VarChar(10) ) +
   ', Bar: ' + Cast( ( select Count(42) from Bar) as VarChar(10) )
 select @Summary

Answer 2

Since SQL Server currently does not have any support for something like string.Format, you have to do:

DECLARE @msg VARCHAR(MAX)

DECLARE @Table1Count INT
SELECT @Table1Count = COUNT(*) FROM dbo.Table1 

DECLARE @Table2Count INT
SELECT @Table2Count = COUNT(*) FROM dbo.Table2

..... and so forth for each table you want to consider

SET @msg = 'Process Succeeded; ' + 
    'Table1 has ' + CAST(@Table1Count AS VARCHAR(10)) + ' rows, ' + 
    'Table2 has ' + CAST(@Table2Count AS VARCHAR(10)) + ' rows...' + ........

Insert INTO LOG (msg) VALUES (@msg)

Right now, in SQL Server, this is a pain - that's why it might be a lot easier to just return those counts to a front-end app and do the formatting and log writing there.

SQL Server 2012 will have a FORMAT function (long overdue!)