Stored Procedure Count

Question

I am trying to get a dynamic count to print out but it is telling me @totalCAUUpdates needs a scalar value. Any thoughts?

declare @totalCAUUpdates as int = 0;
   declare @realTableName as varchar(100) = '[_TEMP_SubscriptionTransactionsForMosoPay09022014]'
   declare @updateSQL as varchar(1000) = 'select @totalCAUUpdates = count(*) from ' + @realTableName + ' where len(accountNumberUpdated) > 0 OR len(accountAccessoryUpdated) > 0;';
   raiserror (@updateSQL, 0,1) with nowait;
   EXEC (@updateSQL);

Answer 1

DECLARE @totalCAUUpdates INT= 0;
DECLARE @updateSQL NVARCHAR(MAX); 
DECLARE @realTableName SYSNAME;

SET @realTableName  = '_TEMP_SubscriptionTransactionsForMosoPay09022014';

SET @updateSQL = N'select @totalCAUUpdates = count(*) from ' + QUOTENAME(@realTableName) 
              + N' where len(accountNumberUpdated) > 0 OR len(accountAccessoryUpdated) > 0;';

EXECUTE sp_executesql @updateSQL
                    ,N'@totalCAUUpdates INT OUTPUT'
                    ,@totalCAUUpdates OUTPUT