Wie behebt man den Fehler, dass die skalare Variable deklariert werden muss, wenn auf die Tabellenvariable verwiesen wird?
Ich kann nicht herausfinden, warum (oder vielleicht können Sie dies einfach nicht tun) ich den Out-of-Scope-Fehler erhalte
Muss die skalare Variable "@CompanyGroupSites_Master" deklarieren.
Kann ich also nicht auf diese Weise innerhalb des Cursors auf meine Tabellenvariable zugreifen, oder muss ich etwas Einfaches übersehen haben, das diese Tabellenvariable beim Verweisen innerhalb des Cursorkörpers außerhalb des Gültigkeitsbereichs hält?
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