Note that @@rowcount is only updated on statements that affect rows within the database - INSERT, UPDATE, or DELETE - and is not updated by a SELECT or UNLOAD statement.

There is currently no direct way of knowing how many rows were written to the file by the UNLOAD ... SELECT statement.

One method would be to first select the rows into a temp table and then output the rows from the temp table as well as count the number of rows in the temp table. E.g.

BEGIN
  DECLARE @count int;

SELECT *
    INTO #temp
    FROM sys.systable;
  SET @unload_statement = 'UNLOAD SELECT * FROM #temp to '''
              || @output_directory || @output_file || ''' '
              || @output_options;
  execute immediate @unload_statement;
  SET @count = ( select count(*) from #temp );
END;

The drawback to using the temp table is that all rows need to be copied. If the data set is small then this may not be a concern but if you are unloading millions of rows (or very wide rows) and the data set does not all fit into the server cache then this operation may take some time to complete.

A second method would be to read the contents of the file that was just written and count the number of lines (i.e. count the number of \x0A characters).

HTH

You could run an additional select statement just to count the records in the result set. (Of course, that would not really count how many rows are unloaded but it should return how many have to be unloaded, and that should match exactly as long as UNLOAD works correct.)

It omits the copying of the rows in contrast to Mark's suggestion. Whether it is more expensive will depend on the complexity of the SELECT statement.

Basically you can use your SQL statement as a derived table and use SELECT COUNT(*) over that, such as

SELECT COUNT(*) INTO @cnt FROM (<your SQL statement>) S

(Note: Each column of your SQL statement is supposed to have an actual column name AFAIK, so for computed columns, you will have to use an alias.)

As you use a dynamic SQL statement, the SELECT COUNT(*) must be run with EXECUTE IMMEDIATE, too.

[The following 2 sentences are not true - see Mark's comment]:

In order to get the number into @cnt, you can't use an ordinary local variable as that would not be accessible in the EXECUTE IMMEDIATE statement. However, you can use a CREATE VARIABLE-created one.

Simplified sample code with a local variable:

BEGIN
  DECLARE @select_statement long varchar;
  DECLARE @count_statement long varchar;
  DECLARE @cnt integer;
  SET @select_statement  =
    'SELECT  * FROM SYS.SYSTABLE  where table_name like ''sys%'' ORDER BY 1';
  SET @count_statement =
    'SELECT COUNT(*) INTO @cnt FROM (' + @select_statement + ') S';
  EXECUTE IMMEDIATE @count_statement;
  MESSAGE 'Number of records: ' || @cnt;
END;