How to fix "Must declare the scalar variable" error when referencing table variable?
36,776
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
Author by
PositiveGuy
Updated on June 29, 2020Comments
-
PositiveGuy almost 4 years
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