0
select top 0 * INTO #temp from stored procedure

Need to create temp table based on the structure of data type returned from stored procedure. Using sql server 2000,2005, 0r 2008

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
ShaoKhan
  • 33
  • 1
  • 7
  • 1
    Some good [post](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) – bonCodigo Jan 06 '13 at 11:17

1 Answers1

0

You can't do this. To get the results from a stored procedure, you have to first define the structure of the results:

create table #temp ( . . . );
insert into #temp
    exec(stored procedure)

If you examine the syntax for the SELECT statement (here), you'll see no reference to running a stored procedure.

Perhaps you should post another question describing what you are trying to do. Why would a stored procedure be returning different result formats?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    thanks for reply, I want to create structure from stored procedure, because being sql developer most of the times we work with temp tables and this temp tables are clone structure of stored procedures and most of the times stored Procedure returns lot of columns and to create structure of this type becomes lengthy process, However what i am doing to resolve this problem is: 1. Create clone copy of original SP 2. Alter clone SP so that it prints QUERY 3. Then modifying this query to use SELECT TOP 0 * INTO from this query
     – ShaoKhan Jan 07 '13 at 04:34
  • There may be other approaches you can take by returning XML or having a separate stored procedure (or argument) that returns the structure and then creating it dynamically, for instance. – Gordon Linoff Jan 07 '13 at 14:03
  • I never before using XML can you provide some examples or link. – ShaoKhan Jan 09 '13 at 04:27
  • An excellent discussion on sharing data from stored procedures is by Erland Sommarskog at http://www.sommarskog.se/share_data.html. – Gordon Linoff Jan 09 '13 at 14:11