sql server - stored - ¿Cómo corregir el error “Debe declarar la variable escalar” al hacer referencia a la variable de tabla?

variables en sql server 2012

Question

No puedo entender por qué (o tal vez simplemente no puedes hacer esto) obtengo el error fuera de alcance

Debe declarar la variable escalar "@CompanyGroupSites_Master".

Entonces, ¿es que no puedo acceder a mi variable de tabla de esta manera dentro de mi cursor, o debo haber omitido algo simple que mantiene esa variable de tabla fuera del alcance cuando se hace referencia desde dentro del cuerpo del cursor?

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

Answer 1

Este es un problema de larga data analizador . Debe deshacerse del prefijo de la tabla o envolverlo entre corchetes.

es decir

delete from @CompanyGroupSites_Master 
where CompanyGroupID = @CompanyGroupID

o

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

No

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