For a start.

You do not need to run the same query twice:

SELECT @Count=count(*) from dbo.PropertyAssets pa

inner join Scenarios sce on sce.id = pa.scenarioId

inner join Portfolios por on por.id = sce.PortfolioId

where por.Name = 'Healthcare Real Estate Active Portfolio' and pa.Id=@PropertyAssetID

.......

SELECT @ActiveRecord=count(*) from dbo.PropertyAssets pa

inner join Scenarios sce on sce.id = pa.scenarioId

inner join Portfolios por on por.id = sce.PortfolioId

where por.Name = 'Healthcare Real Estate Active Portfolio' and (pa.status IS NULL or pa.status = '') and pa.Id=@PropertyAssetID

Do it in one go:

SELECT @Count=count(*) ,

@ActiveRecord=count(CASE WHEN (pa.status IS NULL or pa.status = '') THEN 1 ELSE NULL END )

FROM dbo.PropertyAssets pa

inner join Scenarios sce on sce.id = pa.scenarioId

inner join Portfolios por on por.id = sce.PortfolioId

where por.Name = 'Healthcare Real Estate Active Portfolio' and pa.Id=@PropertyAssetID