?
Solved

Sp_executesql

Posted on 2003-03-15
7
Medium Priority
?
1,856 Views
Last Modified: 2012-06-27
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
Comment
Question by:Prabhakar_Aluri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Accepted Solution

by:
tony_o1 earned 80 total points
ID: 8143654
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
 
LVL 34

Expert Comment

by:arbert
ID: 8143739
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8152046
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 34

Expert Comment

by:arbert
ID: 8718162
What's the status of this question?
0
 

Expert Comment

by:CleanupPing
ID: 9275845
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183298
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question