Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1866
  • Last Modified:

Sp_executesql

to execute a statement with sp_Executesql statement
i have declared a temp table in a procedure
and i am trying to insert values in it like
@sql = 'insert into @My_out_tab ' + ' select.....'
exec sp_executesql @sql
then it is giving error like


Must declare variavble @My_out_table

but i have already declared it in script
0
Prabhakar_Aluri
Asked:
Prabhakar_Aluri
1 Solution
 
tony_o1Commented:
instead of using a table variable, you have to use a temp table.

change
declare @My_Table_Out (...)

to

create table #My_Table_Out(...)

and change the  'insert into @My_out_tab ' + ' select.....'

to

 'insert into #My_out_tab ' + ' select.....'

0
 
arbertCommented:
If your just trying to pass a table name--try this:

@sql = 'insert into' +  @My_out_tab  + ' select.....'
exec sp_executesql @sql



You were close, you just have your apostrophes a little wrong....

Brett


0
 
Scott PletcherSenior DBACommented:
Tony_01 is correct:
Dynamic execution is considered a separate batch, so no variables from the outer query, no matter what datatype, are available to EXEC sp_executesql.  Sp_executesql will never be able to see a table variable from a SP or other code.  You need to use a temp table.

If necessary, after coming back from the dynamic SQL you can copy the contents of the temp table into a table variable.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arbertCommented:
What's the status of this question?
0
 
CleanupPingCommented:
Prabhakar_Aluri:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
monosodiumgCommented:
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: tony_o1 http:#8143654

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now