Having trouble correctly using @declare in an iter

Question

I posted another question here

But it turns out that the first fix to my issue was more simple and that I'd placed the setting up my count to iterate in the wrong location. The real issue is that the value that I'm constructing the name of the DB wrong when I attempt to iterate.

Here is the code that was corrected by a friendly individual on Stack Overflow and they notified me to post a second question to clarify this issue with the incorrect declaration format.

The main issue involves this line [EDDS'+cast(@databasename as nvarchar(128))+'].[EDDSDBO].[Document]

--check if the #databases table is already present and then drop it
IF OBJECT_ID('tempdb..#databases', 'U') IS NOT NULL
    drop table #databases;


--create the temp table as outside the loop
create table #databases(
    ID INT IDENTITY,
    ArtifactID VARCHAR(20) -- not sure of this ID's data type
)


--check if your temp table exists and drop if necessary
IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL
    drop table #temptable;

--create the temp table as outside the loop
create table #temptable(
    fileSize dec,
    extractedTextSize dec
)

--this will allow the population of each database name
DECLARE @databasename sysname = '' 

-- initialze to 1 so it matches first record in temp table
DECLARE @LoopOn int = 1; 

--this will be the max  count from table
DECLARE @MaxCount int = 0; 

--Once this first statement has been run there will now be a number column 
that is associated with the artificatID. Each database has an area that is 
--      titled [EDDS'artifactID']. So if the artifactID = 1111111 then the 
DB would be accessed at [EDDS1111111]

-- do insert here so it adds the ID column
INSERT INTO #databases(
    ArtifactID
)
SELECT ArtifactID 
FROM edds.eddsdbo.[Case]
where name like '%Review%'

-- sets the max number of loops we are going to do
select @MaxCount = COUNT(*) 
FROM #databases;

while @LoopOn <= @MaxCount
    BEGIN
        -- your table has IDENTITY so select the one for the loop your on 
(initalize to 1)
        select @databasename = ArtifactID 
        FROM #databases
        WHERE ID = @LoopOn;

        --generate your sql using the @databasename variable, if you want 
to make 
        --the database and table names dynamic too then you can use the 
same formula

        insert into #temptable
        select SUM(fileSize)/1024/1024/1024, 
SUM(extractedTextSize)/1024/1024
        -- dont know/think this will work like this?  If not you have to 
use dynamic SQL 
        FROM [EDDS'+cast(@databasename as nvarchar(128))+'].[EDDSDBO]. 
[Document] ed
    where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,- (day(getdate())),getdate()),106))

    -- remove all deletes/etc and just add one to the @LoopOn and it will be selected above based off the ID
    select @LoopOn += 1
end

-- Query the final values in the temp table after the iteration is complete
select filesize+extractedTextSize as Gigs 
FROM #temptable

I'm getting an error that is

Invalid object name 'EDDS'+cast(@databasename as nvarchar(128))+'.EDDSDBO.Document'

If I manually enter EDDS1111111.EDDSDBO.Document it works fine.

If I set declare @databasename nvarchar(128) = 1111111 it also breaks the code. I think the error has something to do with how I'm casting it or adding it into the statement.

Thank you for any help you can offer

Answer 1

You can not build out the SQL and execute like you are above or you get the error you are seeing, you need to make the entire statement dynamic. Below are 2 different solutions to do so depending on size/complexity (in case you didnt post full code).

This will solve your problem and create dynamic SQL for each row in your table.

I updated this to have 2 solutions, one if you have not a lot of rows to execute the dynamic SQL for, and another to use if you have complex or a lot to run on.

The second solution could be trimmed down using less code for your specific scenario, but doing it the way I have below will let you use it for a lot more scenarios by just changing the SQL and temp tables your inserting into for your results.

I tested this myself with some basic sql tables and it worked well, both versions. Though I was not doing calculations like you are so depending on your data in your table your calculations and/or your actual selects may need to be updated as I dont have access to your actual data/tables to test.

Also I have a couple lines in there for testing, you can obviously take those out.

-- this is used to add line breaks to make code easier to read
DECLARE @NewLine AS NVARCHAR(MAX) = CHAR(10)

-- to hold your dynamic SQL for all rows/inserts at once
DECLARE @sql NVARCHAR(MAX) = N'';

-- create temp table to insert your dynamic SQL results into 
IF OBJECT_ID('tempdb..#DatabaseSizes', 'U') IS NOT NULL
    DROP TABLE #DatabaseSizes;

create table #DatabaseSizes(
    fileSize DECIMAL,
    extractedTextSize DECIMAL
)

SELECT @sql = @sql + N'' + 
    'select SUM(fileSize)/1024/1024/1024 as fileSize, SUM(extractedTextSize)/1024/1024 as extractedTextSize ' + @NewLine +          
    'FROM [EDDS' + CAST(ArtifactID as nvarchar(128)) + '].[EDDSDBO].[Document] ed ' + @NewLine +
    'where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,- (day(getdate())),getdate()),106)) ; ' + @NewLine + @NewLine
FROM edds.eddsdbo.[Case]
WHERE name like '%Review%'


-- for testing/validating 
PRINT @sql

INSERT INTO #DatabaseSizes (
    fileSize,
    extractedTextSize
)
-- executes all the dynamic SQL we just generated
EXEC sys.sp_executesql @SQL;


SELECT * 
FROM #DatabaseSizes


-- &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
-- for solution with a LOT of records from your table that is too large or complex to execute all at once using the above 
-- this will generate seperate dynamic SQL for each row in your table
-- &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


DECLARE @NewLine AS NVARCHAR(MAX) = CHAR(10)
DECLARE @IdOnFromLoop INT
DECLARE @DynamicSQLFromLoop NVARCHAR(MAX)

-- table to insert data into that our dynamic SQL creates
IF OBJECT_ID('tempdb..#DatabaseSizes', 'U') IS NOT NULL
    DROP TABLE #DatabaseSizes;

create table #DatabaseSizes(
    fileSize DECIMAL,
    extractedTextSize DECIMAL
)

-- this is to hold each rows dynamic SQL so we can loop through them and execute each statement one at a time
IF OBJECT_ID('tempdb..#DynamicSQLPerLoop', 'U') IS NOT NULL
    DROP TABLE #DynamicSQLPerLoop;

create table #DynamicSQLPerLoop(
    ID INT IDENTITY,
    DynamicSQL NVARCHAR(MAX)
)

-- here we build our our dynamic SQL we want for each row in the table to be executed
INSERT INTO #DynamicSQLPerLoop (
    DynamicSQL
)
SELECT 'select SUM(fileSize)/1024/1024/1024 as fileSize, SUM(extractedTextSize)/1024/1024 as extractedTextSize ' + @NewLine +           
    'FROM [EDDS' + CAST(ArtifactID as nvarchar(128)) + '].[EDDSDBO].[Document] ed ' + @NewLine +
    'where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,- (day(getdate())),getdate()),106)) ; ' + @NewLine + @NewLine
FROM edds.eddsdbo.[Case]
WHERE name like '%Review%'



-- for testing/validating all the rows
SELECT * FROM #DynamicSQLPerLoop

-- need to initalize ID to start on, could default to 1, but if no recordsd found would try to do loop and error out
SELECT @IdOnFromLoop = MIN(ID) 
FROM #DynamicSQLPerLoop

-- now we just loop through all the records, until no more are found
WHILE @IdOnFromLoop IS NOT NULL
    BEGIN
        -- need to get dynamic SQL statement to execute for the loop we are on now
        SELECT @DynamicSQLFromLoop = DynamicSQL
        FROM #DynamicSQLPerLoop
        WHERE ID = @IdOnFromLoop

        -- now we insert the data into our table by executing the dynamic SQL 
        INSERT INTO #DatabaseSizes (
            fileSize,
            extractedTextSize
        )       
        EXEC sys.sp_executesql @DynamicSQLFromLoop


        -- now we get the ID that is one higher than the one we just did, and if none found will exit loop
        SELECT @IdOnFromLoop = MIN(ID) 
        FROM #DynamicSQLPerLoop
        WHERE ID > @IdOnFromLoop

    END -- end looping


SELECT * 
FROM #DatabaseSizes