SQL Server stored procedures calls require variable declaration (but already declared)

Question

I have a few stored procedures which I call in this order.

So, from the first stored procedure, importTaxonomy I call parseXBRL and from parseXBRL I call createTaxonomyStructure.

But in this flow, when the code of the last stored procedure is executed I get an error.

Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@temporaryTable".

Below you can find the first few lines code of this stored procedure:

CREATE PROCEDURE createTaxonomyStructure @taxonomy_table nvarchar(max), @debug bit = 0  
AS

DECLARE @statement NVARCHAR(MAX)
DECLARE @temporaryTable TABLE (taxonomyLine NVARCHAR(MAX))      -- declared a temporary table to avoid creating a Dynamic Query with the entire cursor, but just with the temporary table
DECLARE @taxonomyLine NVARCHAR(MAX)     -- variable that will store one line of the taxonomy

    SET @statement = 'INSERT INTO @temporaryTable SELECT taxText FROM ' + @taxonomy_table   -- statement that will import the taxonomy in the temporary table

EXEC sp_executesql @statement           

DECLARE taxonomyCursor CURSOR READ_ONLY FAST_FORWARD FOR        -- read each line in the taxonomy to parse afterwards
    SELECT taxonomyLine
    FROM @temporaryTable

OPEN taxonomyCursor

FETCH NEXT FROM taxonomyCursor INTO @taxonomyLine               -- parsing each taxonomy line and extracting the values from important attributes
WHILE @@FETCH_STATUS = 0
    BEGIN

        DECLARE @id_element NVARCHAR(MAX)
        DECLARE @leaf_element NVARCHAR(MAX)

        SELECT @id_element = (SELECT dbo.extract_IDElement(@taxonomyLine))
        SELECT @leaf_element = (SELECT dbo.extract_IDLeafElement(@taxonomyLine))

        SET @statement = 'UPDATE ' + @taxonomy_table + ' SET fullName = ''' + @id_element + ''', leafName = ''' + @leaf_element + '''';

        EXEC sp_executesql @statement

    END

I do declare this variable, but I still get the error and I don't understand why.

How can I get over this error?

Thanks!

Answer 1

the error is here:

SET @statement = 'INSERT INTO @temporaryTable SELECT taxText FROM ' + @taxonomy_table   -- statement that will import the taxonomy in the temporary table

EXEC sp_executesql @statement 

change it to

set @statement = 'select taxText from ' + @taxonomy_table

insert into @temporaryTable
exec sp_executesql @statement

The error is happening because in the scope of executing @statement there's no variable table @temporaryTable.