If you want a specific value, return it from the proc, don't rely on the "last" statement in the proc yielding the correct @@ROWCOUNT: how is someone later supposed to know they can't add lines to that proc??

To do it properly, you should use an OUTPUT parameter. However, in a hurry you might use the return code from the proc itself. Be sure then to return negative values for errors.

...

SELECT [StateProvinceID],[StateProvinceCode],[Name]

FROM[AdventureWorks2012].[Person].[StateProvince]

SET @rowcount = @@ROWCOUNT

Drop Table #tbEmployee

RETURN @rowcount

EXEC @rowcount = ...

--@rowcount now contains the row count from the desired SELECT in the proc