sp_executesql invalid object name

https://stackoverflow.com/questions/10417126/dynamically-named-temp-table-returns-invalid-object-name-when-referenced-in-st

You are doing it wrong!

Try:

exec(@SQL)

instead of:

EXECUTE sp_executesql @SQL

---

To use sp_executesql the variable must be inside @SessionID the quotes and it must be provided has input parameter. Check this for a full example!

---

You've to be aware that Dynamic SQL is a good port for SQL injections!

 

https://stackoverflow.com/questions/11506314/invalid-object-name-temp-in-dynamic-sql-and-array-of-field-names

The temp table is out of scope after exec is done.'exec' or 'sp_executesql' are run in thier own scope.

Thus anything created like temp tables and variables are destroyed or out of scope as soon as execution is over .Think these like stored proc.

To fix the issue.Create the temp table in main code.. then insert into it using dynmic sql and then read it in main code.

 

 

http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong

 

 

 

作者:Chuck Lu    GitHub