Using @variable in a While statement

[bmacbmac]

Hi Everyone,

I have the following table:

create table August2018 (counter int identity(1,1), County varchar(25), Locates int, Billed varchar(1), ToBill varchar(1))

Insert into August2018  (county, tobill) values ('Bellingham', 'x')
Insert into August2018 (county, tobill) values ('Adams', NULL)
Insert into August2018 (county, tobill) values ('Brighton', 'x')
Insert into August2018  (county, tobill) values ('Farmington', 'x')
Insert into August2018  (county, tobill) values ('Lewis', NULL)

The 'County' column refers to a specific database name. I need to calculate the 'Locate' total for each of the databases where tobill = 'x'.

If I were to calculate the 'Locate' total manually for each 'Countyname' I would do this:

update August2018
set locate = (select sum(convert(int,sfield1)) from bellingham..userfield)
where countyname = 'bellingham'

update August2018
set locate = (select sum(convert(int,sfield1)) from brighton..userfield)
where countyname = 'brighton'
update August2018
set locate = (select sum(convert(int,sfield1)) from farmington..userfield)
where countyname = 'farmington'

I would like a script to do them all for me. So I have two questions:

1. Is a while loop the best way to go here? This script works for me:

while (select count(counter) from august2018 where tobill is not null and billed is null) > 0
begin

declare @countyname varchar(100)
set @countyname = (select min(countyname) from august2018 where tobill is not null and billed is null)

declare @locates nvarchar(1000)
set @locates = 'update august2018 set locates = (select sum(convert(int,sfield1)) from ' + @countyname + '..userfield) where county = ' + '''' + @countyname + ''''
print @locates
exec sp_executeSQL @locates

update august2018 set tobill = NULL, 
billed = 'x'
where countyname = @countyname
end

Question2:
The table name 'August2018' will not be the same each time. Earlier in the script I have this table created as a variable @Billingdate. I would like to replace "August2018" with "@billingdate" because it was already set as an @variable wayyyy back in my script.

This script works great, but I can't find a way to replace August2018 with @billingdate in the 'while' row and the 'set @countyname' row.

declare @billingdate varchar(100)
set @billingdate = 'august2018'
while (select count(counter) from august2018 where tobill is not null and billed is null) > 0
begin

declare @countyname nvarchar(100)
set @countyname = (select min(county) from august2018 where tobill is not null and billed is null)

declare @locates nvarchar(1000)
set @locates = 'update ' + @billingdate + ' set locates = (select sum(convert(int,sfield1)) from ' + @countyname + '..userfield) where county = ' + '''' + @countyname + ''''
print @locates
exec sp_executeSQL @locates

declare @tobill nvarchar(100)
set @tobill = 'update ' + @billingdate + ' set tobill = NULL, billed = ' + '''' + 'x' + '''' + 'where county = ' + '''' + @countyname + ''''
print @tobill
exec sp_executeSQL @tobill
end

Am I making this more difficult than it needs to be?