33

I can't figure out why (or maybe you just can't do this) I get the out of scope error

Must declare the scalar variable "@CompanyGroupSites_Master.

So is it that I cannot access my Table variable this way inside my Cursor, or I must have missed something simple that's keeping that table variable out of scope when referencing from within the cursor body?

DECLARE @TotalCompaniesToProcess int 
SET @TotalCompaniesToProcess = (select distinct Count(BusinessLine) from vwBuisinessUnit)

IF(@TotalCompaniesToProcess > 0)
BEGIN

    ---------------- ############# SETUP ############# ----------------

    DECLARE @Companies TABLE (Company varchar(30))        
    Insert Into @Companies select distinct Company from Companies

    DECLARE @CompanyGroups_Added TABLE(
                                        CompanyGroupDesc varchar(50),
                                        size varchar(50)
                                      )

    DECLARE @CompanyGroupSites_Added TABLE (
                                              CompanyGroupID int,
                                              CompanyID varchar(12)                                    
                                           )

    DECLARE @CompanyGroupSites_Master TABLE (
                                              CompanyGroupID int,
                                              CompanyID varchar(12)
                                           )
    Insert into @CompanyGroupSites_Master select CompanyGroupID, CompanyID from Sec.CompanyGroupSites                        

    DECLARE @CompanyGroupID int                         
    DECLARE @Company int

    ----------------  END SETUP ----------------

    ---------------- UPDATE THE COMPANIES ----------------

    DECLARE Companies_Cursor CURSOR FOR (select distinct BusinessLine from vwBuisinessUnit)  
    --select distinct BU, BusinessLine from vwBusinessUnit

    Open Companies_Cursor

    Fetch NEXT FROM Companies_Cursor into @Company

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- is there an existing CompanyGroup for this Organization?  If not create it
        SET @CompanyGroupID = ( select CompanyGroupID from Sec.CompanyGroup 
                                where size = 'Business'
                                and CompanyGroupDesc = @Company)

        IF(@CompanyGroupID < 1) 
        BEGIN
            INSERT INTO @CompanyGroups_Added ([CompanyGroupDesc], [Size])
            VALUES (@Company, 'Business')

            SET @CompanyGroupID = @@IDENTITY

            select @CompanyGroupID as CompanyGroupID_Added -- testing
        END

        Select ToDelete.* from (
            select CompanyGroupID, Company 
            from @CompanyGroupSites_Master 
            where @CompanyGroupSites_Master.CompanyGroupID = @CompanyGroupID) as ToDelete

        delete from @CompanyGroupSites_Master where @CompanyGroupSites_Master.CompanyGroupID = @CompanyGroupID

        Fetch NEXT FROM Companies_Cursor into @Company
    END


    CLOSE Companies_Cursor
    DEALLOCATE Companies_Cursor

END
Chait
  • 1,052
  • 2
  • 18
  • 30
PositiveGuy
  • 46,620
  • 110
  • 305
  • 471
  • 3
    why are you using a cursor for this? ANd why oh why are you using @@identity when you shoudl be using OUTPUT or scope_identity() (@@Identity can causee serious data integrity problems and shouldnot be used this way) – HLGEM May 24 '12 at 21:41
  • right now using a cursor cause I don't know T-SQL for a hole in the ground for iterating loops. I'll change this for the better after I get it working. Crap yea it's been a while, forgot about scope_identity – PositiveGuy May 24 '12 at 22:01
  • what could I use other than a cursor? Because I'm not simply doing one crud operation which can be done in a 2 liner to replace this iterative loop. Advice? – PositiveGuy May 24 '12 at 22:03
  • 1
    @CoffeeAddict - Not sure exactly what your code is doing. It has table variables that aren't used for anything so I presume that it isn't finished yet? If you are on SQL Server 2008/2012 you can look at `MERGE` to do your "if not exists" logic in a set based way rather than row by row. You can use the `OUTPUT` clause to retrieve details of the inserted rows (possibly into a table variable). BTW. `SELECT COUNT(BusinessLine)` will always return exactly one row so `DISTINCT` is redundant there. – Martin Smith May 24 '12 at 22:12

1 Answers1

68

This is a long standing parser issue. You need to get rid of the table prefix or wrap it in square brackets.

i.e.

delete from @CompanyGroupSites_Master 
where CompanyGroupID = @CompanyGroupID

or

delete from @CompanyGroupSites_Master 
where [@CompanyGroupSites_Master].CompanyGroupID = @CompanyGroupID

Not

delete from @CompanyGroupSites_Master 
where @CompanyGroupSites_Master.CompanyGroupID = @CompanyGroupID
Martin Smith
  • 438,706
  • 87
  • 741
  • 845