Must declare the scalar variable

Question

I don't understand why I keep getting this error. Anyone can help me understand why this error is coming up, would be much appreciated. I am trying to update a large database and trying to replace the default location string for all the attachments with 'D:'. I feel like I am close, but I do not understand this error. This query successfully loops through all my table names saved in @Tables, but at each one I get the error. Here is my code... Error: Must declare the scalar variable "@oldPath"

DECLARE @newPath VARCHAR(500);
SET @newPath = 'D:\temp';

DECLARE @oldPath VARCHAR(500);
SET @oldPath = (SELECT Value FROM Settings WHERE Name = 'AttachmentDirectoryPath');



DECLARE @Tables TABLE
(
    myTableName VARCHAR(500)
)

insert into @Tables(myTableName) values ('WorkOrderMasterAttachments');
insert into @Tables(myTableName) values ('BillOfMaterialAttachments');
insert into @Tables(myTableName) values ('CustomerAttachments');
insert into @Tables(myTableName) values ('ToolAttachments');
insert into @Tables(myTableName) values ('FacilityAttachments');
insert into @Tables(myTableName) values ('LocationAttachments');
insert into @Tables(myTableName) values ('AssetAttachments');
insert into @Tables(myTableName) values ('ContractorAttachments');
insert into @Tables(myTableName) values ('VendorAttachments');
insert into @Tables(myTableName) values ('WorkOrderAttachments');
insert into @Tables(myTableName) values ('ClosedWorkOrderAttachments');
insert into @Tables(myTableName) values ('EmployeeAttachments');
insert into @Tables(myTableName) values ('PurchaseOrderAttachments');
insert into @Tables(myTableName) values ('PurchaseOrderHistoryAttachments');
insert into @Tables(myTableName) values ('PartAttachments');
insert into @Tables(myTableName) values ('AssetSystemAttachments');

DECLARE cursor_tables cursor
FOR SELECT myTableName from @Tables

OPEN cursor_tables

DECLARE @loopTableName VARCHAR(100)

fetch next from cursor_tables
into @loopTableName

DECLARE @updateSql VARCHAR(2000);

WHILE @@FETCH_STATUS = 0
BEGIN   

    SET @updateSql = ' UPDATE ' + @loopTableName + 
                 ' SET Path = REPLACE(Path,@oldPath,@newPath)'

    --print @updateSql
    EXEC(@updatesql)

    fetch next from cursor_tables
    into @loopTableName
END 

CLOSE cursor_tables
DEALLOCATE cursor_tables

Answer 1

Here is a much simpler approach than using a loop for what is likely not something you need to run very frequently.

DECLARE @newPath VARCHAR(500) = 'D:\temp';

DECLARE @oldPath VARCHAR(500);
SELECT @oldPath = Value 
FROM Settings 
WHERE Name = 'AttachmentDirectoryPath'

update WorkOrderMasterAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update BillOfMaterialAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update CustomerAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update ToolAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update FacilityAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update LocationAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update AssetAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update ContractorAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update VendorAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update WorkOrderAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update ClosedWorkOrderAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update EmployeeAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update PurchaseOrderAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update PurchaseOrderHistoryAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update PartAttachments SET Path = REPLACE(Path, @oldPath, @newPath)
update AssetSystemAttachments SET Path = REPLACE(Path, @oldPath, @newPath)

--EDIT--

Since you stated that you really want to use a table to get all the table names to update here is an approach using dynamic sql that doesn't need a loop. It builds up a long string of sql statements to update each table but doesn't use a loop. I realize that the performance of 15-16 iterations isn't much but this example is a way to avoid to loops and in my opinion is much easier code to debug. It is also for me to conceptualize this type of thing instead of using a loop.

DECLARE @Tables TABLE
(
    myTableName VARCHAR(500)
)

insert into @Tables(myTableName) values ('WorkOrderMasterAttachments');
insert into @Tables(myTableName) values ('BillOfMaterialAttachments');
insert into @Tables(myTableName) values ('CustomerAttachments');
insert into @Tables(myTableName) values ('ToolAttachments');
insert into @Tables(myTableName) values ('FacilityAttachments');
insert into @Tables(myTableName) values ('LocationAttachments');
insert into @Tables(myTableName) values ('AssetAttachments');
insert into @Tables(myTableName) values ('ContractorAttachments');
insert into @Tables(myTableName) values ('VendorAttachments');
insert into @Tables(myTableName) values ('WorkOrderAttachments');
insert into @Tables(myTableName) values ('ClosedWorkOrderAttachments');
insert into @Tables(myTableName) values ('EmployeeAttachments');
insert into @Tables(myTableName) values ('PurchaseOrderAttachments');
insert into @Tables(myTableName) values ('PurchaseOrderHistoryAttachments');
insert into @Tables(myTableName) values ('PartAttachments');
insert into @Tables(myTableName) values ('AssetSystemAttachments');

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'Update ' + myTableName + ' set Path = REPLACE(Path,@oldPath,@newPath);'
from @Tables

select @SQL

exec sp_executesql @SQL, N'@oldPath varchar(500), @newPath varchar(500)', @oldPath = @oldPath, @newPath = @newPath

Answer 2

Use SP_EXECUTESQL to pass values to variables inside dynamic query

DECLARE @updateSql NVARCHAR(2000) -- important 

SET @updateSql = ' UPDATE ' + QUOTENAME(@loopTableName) + 
             ' SET Path = REPLACE(Path,@oldPath,@newPath)'

--print @updateSql
EXEC sp_executesql @updatesql,
     N'@oldPath VARCHAR(500),@newPath VARCHAR(500)',
       @oldPath = @oldPath, @newPath = @newPath

sp_executesql requires @stmt parameter to be of NVARCHAR type so change the datatype of @updateSql from VARCHAR to NVARCHAR

Answer 3

Strings don't automatically know that they contain variables. So, forget about EXEC() as a general method for executing dynamic SQL. Remember: sp_executesql instead make the inner loop:

SET @updateSql = ' UPDATE ' + @loopTableName + 
             ' SET Path = REPLACE(Path, @oldPath, @newPath)'

exec sp_executesql @updatesql,
     N'@oldPath varchar(500), @newPath varchar(500)',
     @oldPath = @oldPath, @newPath = @newPath;

This will execute the SQL and assign the variables. Note: You cannot pass in the table name as a parameter into the query, so you still need to provide that directly in the string.