在迭代中正确使用@declare时遇到麻烦 [英] Having trouble correctly using @declare in an iteration

问题描述

我在此处

但是事实证明，对我的问题的第一个解决方法更加简单，并且我将设置计数的位置放在了错误的位置. 真正的问题是，当我尝试迭代时，我在构造数据库名称的值是错误的.

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.

这是一个友好的个人在Stack Overflow上更正的代码，他们通知我发布第二个问题，以使用错误的声明格式来澄清此问题.

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.

主要问题涉及此行 [EDDS'+ cast(@databasename as nvarchar(128))+'].[EDDSDBO].[Document]

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

我遇到的错误是

无效的对象名称'EDDS'+ cast(@数据库名称为nvarchar(128))+'.EDDSDBO.Document'

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

如果我手动输入EDDS1111111.EDDSDBO.Document，则效果很好.

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

如果我设置了declare @databasename nvarchar(128) = 1111111，它也会破坏代码. 我认为该错误与我将其强制转换或将其添加到语句中的方式有​​关.

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.

感谢您提供的任何帮助

推荐答案

您不能像上面那样构建SQL并执行，否则您将看到错误，则需要使整个语句动态化.下面是根据大小/复杂度(如果您未发布完整代码)的两种不同解决方案.

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).

这将解决您的问题，并为表中的每一行创建动态SQL.

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

我将其更新为2种解决方案，一种用于没有太多行要执行动态SQL的解决方案，另一种用于要运行复杂或太多的行.

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.

第二种解决方案可以针对特定情况使用更少的代码进行精简，但是按照我下面的方法进行操作，只需更改要插入结果的SQL和临时表，即可将其用于更多情况

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.

我自己用一些基本的sql表对此进行了测试，并且两个版本都运行良好. 尽管我没有像您那样进行计算，但根据您表中的数据，由于我无权访问要测试的实际数据/表，因此可能需要更新计算和/或实际选择的内容.

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

这篇关于在迭代中正确使用@declare时遇到麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！