Showing "Invalid object name " in sqlServer?

Question

While Executing the Following query it showing the Invalid object name '#temp1'. can any body knows the error occurred due to which reason this is my orginal code i used to fetch code , her differnt tables are formed i need to get the sum of the each row of each table

DECLARE @t TABLE (
                        id int IDENTITY(1,1),
                        BranchName nvarchar(max)
                     )

DECLARE @n int = 0,
        @i int = 1,
        @BranchName nvarchar(max),
        @sql nvarchar(max),
        @columns nvarchar(max)

INSERT INTO @t
    SELECT DISTINCT BranchName
    FROM ALX_Branches

    SELECT @n = @@ROWCOUNT

WHILE @n >= @i
BEGIN
    SELECT @BranchName = BranchName
    FROM @t
    WHERE id = @i
    SELECT @columns = (
                        SELECT DISTINCT ','+QUOTENAME([SubInventory])
                        FROM #MyTempTable
                        WHERE [BranchName] = @BranchName
                        FOR XML PATH('')
                      )

SELECT @sql = N'--
                    SELECT * into #temp1
                    FROM (
                            SELECT [BranchID],
                            [SubInventory],
                            [Product],
                            [Stock] 

                    FROM #MyTempTable
                    WHERE [BranchName] = ''' +@BranchName +'''
                    ) as t
                PIVOT (
                MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')

        ) as pvt'

    EXEC sp_executesql @sql

    select * from #temp1

you need to create your table first then it will be available in the dynamic sql - from here http://stackoverflow.com/questions/2917728/t-sql-dynamic-sql-and-temp-tables — Andrey Morozov, Oct 06 '16 at 12:11
`PIVOT ( MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')` this ,may be change each time , according to the branches, then how can i create a stable table for this — , Oct 06 '16 at 12:20
IDK :) try to use a simple persistent table and drop it after... hm? — Andrey Morozov, Oct 06 '16 at 12:30
here many tables are formed according to each branch name,so can u provide another method to solve this method. — , Oct 06 '16 at 12:34
you can use ##temp1 instead of #temp1, just remember to drop it after you used it — Jayvee, Oct 06 '16 at 13:34

score 0 · Answer 1 · answered Oct 06 '16 at 16:54

Firstly, there is no need for creating #temp1 table before.

because you are using "Select * into" that already create table within it.

Suppose type this note as a comment, but I don't have enough reputation score.

The reason of

Invalid object name '#temp1'

is: the variable @sql is NULL because #temp1 is not created yet via "Select * into" clause.

so append selecting from #temp1 within dynamic sql as the following:

SELECT @sql = N'--
                SELECT * into #temp1
                FROM (
                        SELECT [BranchID],
                        [SubInventory],
                        [Product],
                        [Stock] 

                FROM #MyTempTable
                WHERE [BranchName] = ''' +@BranchName +'''
                ) as t
            PIVOT (
            MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')

    ) as pvt
    select * from #temp1 '

EXEC sp_executesql @sql

Showing "Invalid object name " in sqlServer?

1 Answers1