SQL Server Trigger - Invalid Object Name

Hi,
I have the following code in a trigger to track changes on specific tables. I will move the code later in a stored procedure, once I get it right.
My question is this one: From the code below, why would SQL Server generate an error statement that #tmptrackchange, inserted and deleted tables are INVALID OBJECT NAMES. This only happens when I decided to build a sql statement based on fields to track and run it with EXECUTE().
When changed from temp to permanent tables, it works. I can't afford using permanent table here.
Any input is welcome.

      DECLARE @pTableName VARCHAR(50)='orders';
      DECLARE @pKey VARCHAR(30)='ID';
      DECLARE @pFieldsList VARCHAR(8000)='';
      DECLARE @pFieldsListConv VARCHAR(8000)='';
      DECLARE @pStmt VARCHAR(8000)='';
      
      /*List of Fields Excluding ID*/       
      SELECT @pFieldsList += ', [' + column_name + ']',
            @pFieldsListConv += ', CONVERT(VARCHAR,[' + column_name + ']) AS [' + column_name + '] '
      FROM information_schema.columns
      WHERE table_name=@pTableName
            AND column_name NOT IN ('' + @pKey + '')  
            AND data_type NOT IN ('TEXT','NTEXT') ;
            
      SET @pFieldsList = ltrim(substring(@pFieldsList, 2, len(@pFieldsList) - 1));
      SET @pFieldsListConv = ltrim(substring(@pFieldsListConv, 2, len(@pFieldsListConv) - 1));
      
      /*Process*/
      IF OBJECT_ID('dbo.#tmpTrackChange') is not null DROP TABLE dbo.#tmpTrackChange;
                  
      -- Case Update
      SET @pStmt = '
            SELECT i.ID, (i.FNAME + ''~['' + d.FVALUE + ''] to ['' + i.FVALUE + '']'') AS CHANGENOTES INTO dbo.#tmpTrackChange
            FROM
            (/*New Record*/
                  SELECT ID, FName, FValue
                        From (Select ' + @pKey + ' as ID, ' + @pFieldsListConv + ' From inserted ) p /*Convert All Fields to VARCHAR*/
                  UNPIVOT
                        (FValue FOR FName IN
                              (' + @pFieldsList + ')
                  ) AS unpvt
            ) i INNER JOIN
            (/*Old Record*/
                  SELECT ID, FName, FValue
                        From (Select ' + @pKey + ' as ID, ' + @pFieldsListConv + ' From deleted) p /*Convert All Fields to VARCHAR*/
                  UNPIVOT
                        (FValue FOR FName IN
                              (' + @pFieldsList + ')
                  )AS unpvt
            ) d ON i.ID=d.ID and i.FNAME=d.FNAME WHERE i.FVALUE<>d.FVALUE;';
      
      EXECUTE (@pStmt);
            
      INSERT INTO dbo.changelog
           ([REVDATE],[REVTIME],[REVBY],
           [CHANGETBL],[RECORDKEY],[CHGNOTES])
     SELECT DISTINCT
           CONVERT(date, getdate()),LEFT(CONVERT(time, getdate()),8),LEFT(HOST_NAME(),50),
            @pTableName, CONVERT(varchar,t1.ID), LTRIM(REPLACE(ca.CHGNotes,'[]','[Empty]'))
           FROM dbo.#tmpTrackChange t1
                   CROSS APPLY (
               SELECT
                  STUFF(
                        (
                        SELECT
                              ', ' + t2.CHANGENOTES
                        FROM dbo.#tmpTrackChange AS t2
                        WHERE t2.ID = t1.ID
                        ORDER BY t2.ID
                        FOR XML PATH ('')
                        )
                       , 1, 1, '')
               ) ca (CHGNotes);
                
           
    IF OBJECT_ID('dbo.#tmpTrackChange') is not null DROP TABLE dbo.#tmpTrackChange;
Omer-PitouAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The problem is the scope. You're creating the temp table in the EXECUTE, which has it's own scope. So when the dynamic SQL is executed and the program flow is returned to your trigger, this inner scope is left. Thus the temp table is cleaned up.

You need to create the temp table before you execute your dynamic SQL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Omer-PitouAuthor Commented:
Thanks...
As I am interested in the data coming out of that sql string execution, I am pretty sure that if I created the temp table before the dynamic SQL execute, I will get an empty table.,, as the one with data belongs to the execute scope.
Does SQL Server has prepare Statement as MySQL?
0
ste5anSenior DeveloperCommented:
No, the scope-cleanup affects only the live-time of the temp table. Not its data. E.g.

CREATE TABLE #tables ( table_name NVARCHAR(MAX) );

DECLARE @Sql NVARCHAR(MAX) = N'
	INSERT INTO	#tables
	SELECT	OBJECT_SCHEMA_NAME(T.object_id) + ''.'' + T.name	
	FROM	sys.tables T
';

EXECUTE ( @Sql );

SELECT	*
FROM	#tables;

DROP TABLE #tables;

-- vs

/*
DECLARE @Sql NVARCHAR(MAX) = N'
	SELECT	OBJECT_SCHEMA_NAME(T.object_id) + ''.'' + T.name
	INTO	#tables
	FROM	sys.tables T
';

EXECUTE ( @Sql );

SELECT	*
FROM	#tables;
*/

Open in new window

0
Omer-PitouAuthor Commented:
Thanks for your time. I combined the two sql strings into one dynamic sql statement and run it once with EXEC.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.