>See bottom of code:
>
>

>
>/**********************************************************************
>* Create a temp cursor of all report category rows
>**********************************************************************/
>-- Drop the temp table if it already exists
>IF OBJECT_ID('tempdb..#SJCategories') IS NOT NULL
>	DROP TABLE #SJCategories
>
>-- Create the temp table
>SELECT ParentTabId, TabOrder, PortalId, TabName, MobileTabName, 
>              AuthorizedRoles, ShowMobile, TabLayout
>	INTO #SJCategories 
>	FROM rbpo.rb_Tabs 
>	WHERE 1=0
>
>-- Add the row number column to the temp table
>ALTER TABLE #SJCategories 
>	ADD RowNum INT IDENTITY(1,1)
>
>-- Pull the San Jose report category rows along with a row number
>INSERT INTO #SJCategories 
>	SELECT ParentTabId, TabOrder, PortalId, TabName, MobileTabName, 
>                             AuthorizedRoles, ShowMobile, TabLayout from rbpo.rb_Tabs
>	WHERE ParentTabId = 47
>
>/**********************************************************************
> **********************************************************************/
>-- Get the total number of category rows & create a counter
>SELECT @MaxCatRows = COUNT(*) FROM #SJCategories
>SET @CatRow = 1
>
>
>SELECT * FROM #SJCategories	-- RowNum is here
>SELECT @CatRow	-- 1
>SELECT * FROM #SJCategories WHERE RowNum = @CatRow	-- This errors with "Invalid column name 'RowNum'."
>

>
>RowNum is in the temp table. Anyone know what's up?

It worked for me after I put GO after ALTER TABLE command. If this is not a solution, I would suggest switching to table variable instead as Borislav showed in another thread - I'm going to try it in a second.

Here is my test

BEGIN TRAN

/**********************************************************************
* Create a temp cursor of all report category rows
**********************************************************************/
-- Drop the temp table if it already exists
IF OBJECT_ID('tempdb..#SJCategories') IS NOT NULL
	DROP TABLE #SJCategories

-- Create the temp table
SELECT Name, GroupName 
	INTO #SJCategories 
	from HumanResources.Department
	WHERE 1=0

-- Add the row number column to the temp table
ALTER TABLE #SJCategories 
	ADD RowNum INT IDENTITY(1,1)

go -- without Go I'm getting an error

-- Pull the San Jose report category rows along with a row number
INSERT INTO #SJCategories (Name, GroupName)
	SELECT Name, GroupName from HumanResources.Department

/**********************************************************************
* Loop once for each category row
**********************************************************************/
-- Get the total number of category rows & create a counter
declare @MaxCatRows int, @CatRow int
SELECT @MaxCatRows = COUNT(*) FROM #SJCategories
SET @CatRow = 1


SELECT * FROM #SJCategories	-- RowNum is here
SELECT @CatRow	-- 1
SELECT * FROM #SJCategories WHERE RowNum = @CatRow	-- This errors with "Invalid column name 'RowNum'."


ROLLBACK