This worked. Thanks!


>>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