> >/********************************************************************** >* 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'." >>
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