Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

insert into #temp exec

Posted on 2004-09-14
9
Medium Priority
?
9,818 Views
Last Modified: 2012-08-13
if sp returns resultset with variable column count, how can i insert it into #temp?
for each name, there is a column in recordset from myproc.
----
create table #temp ( id int, val varchar(100) )
insert into #temp exec myproc 2, 3, 'john'

---
create table #temp ( id int, val varchar(100) , ... )
insert into #temp exec myproc 2, 3, 'john,mary,joe'
0
Comment
Question by:ill
[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
9 Comments
 
LVL 6

Expert Comment

by:OlegP
ID: 12052303
Declare @S varchar(1000)
Declare @A varchar(1000)
Declare @I int
Declare @C int
create table #temp ( id int, val varchar(100) )
set @S= 'john,mary,joe'
SET @C=0
IF LEN(@S)>0
  BEGIN
    SET @I =1
    WHILE @I<=LEN(@S)
       BEGIN
           IF SUBSTRING(@S,@I,1)=',' SET @C=@C+1
           SET @I=@I+1
       END
  END
IF @C<>0
  SET @I=1
  WHILE @I<=@C
    BEGIN
      SET @A='ALTER TABLE #TEMP ADD VAL_'+LTRIM(STR(@I))+' varchar(100)'
      EXEC(@A)
      SET @I=@I+1
    END

insert into #temp exec myproc 2, 3,@S
0
 
LVL 2

Expert Comment

by:vidnan123
ID: 12052484
This is just a suggestion to create and populate the #temp table easily.

Your stored procedure myproc must definitely have a SELECT statement to return the record-set on execution.

My suggestion is to have the following SQL statement within your stored procedure

SELECT col1,col2,col3...........
INTO #TEMP
FROM ..........

This 'SELECT ...INTO #TEMP' statement basically creates the temperory table with the same number of columns as the output of the SELECT statement, and also populates it with the output of the SELECT statement.

Hope you find that this suits your purpose.

(PS: In this case you would not need the following SQL execution
insert into #temp exec myproc 2, 3, 'john'.......)
0
 
LVL 12

Author Comment

by:ill
ID: 12055183
thx for comments so far, but i got problem
@OlegP
your solution works in specific cases, when the names are unique.only one 'john' is in database.
number of 'john' is only know in scope of "myProc"
trying to do it for two john's...  without success yet :(

@vidnan123
afain, select into cannot be used with exec.


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

by:Hilaire
ID: 12055276
Hi ill,

Unfortunately there's no metadata about stored procedure output AFAIK.

This could be achieved for a table-valued user-defined function :
you could use information_schema.routine_columns to find out the structure of the resulting table, build it on the fly using a loop or dynamic SQL.

But theres' no such thing for stored procedures.
Since Dynamic SQL is allowed in stored procedures, SQL Server doesn't know in advance the structure of the output table(s) - not to mention that stored procedures can have several resultsets - ...

I may be wrong, but I thought about it all day and tend to think you can't detect the output structure at run time.

Hilaire

0
 
LVL 12

Author Comment

by:ill
ID: 12055388
@Hillaire
me too and.... think the same as you.
but still hope, that someone has a solution or workaround.
procedure is  calling other SPs and execs, so UDF cannot be used.
0
 
LVL 2

Accepted Solution

by:
BoyPupa earned 1200 total points
ID: 12061206
There is one solution of which I'm aware:

select * into #temp
      from openquery(<serverName>,'exec <db>.dbo.myproc 2, 3, ''john'' ')

of course openquery can't deal with variables, so if you want to ever change the parameters you pass in (btw you won't be getting any output params, or return values,or print statements/debugging messages, and you'd better have nocount set to on, and ... just make sure the proc issues exactly 1 select statement) the actual solution would have to be more like

declare @stmt varchar(8000)
declare @UniqueTempName varchar(50)
select @UniqueTempName = '##'+replace(convert(varchar(50),newid()),'-','_')
--to handle concurrent proc calls create a global temp table with a unique name

declare @param1 int
declare @param2 int
declare @param3 varchar(50)
set @param1 = 2
set @param2 = 3
set @param3 = 'john'
set @stmt = '
select * into '+@UniqueTempName+'
    from openquery(<serverName>,''exec <db>.dbo.myproc '
            +convert(varchar,@param1)
            +','+convert(varchar,@param2)
            +', '''''+@param3+''''''')
SELECT *FROM tempdb.INFORMATION_SCHEMA.COLUMNS
      where table_name = '''+@UniqueTempName+'''
drop table '+@UniqueTempName+'
'
print @stmt
exec (@stmt)

Some notes:
1.    using openquery to generate the metadata for you essentially calls the proc twice, once with FMTONLY ON
   depending on what kind of dynamic stuff you're doing inside the proc (I'm guessing plenty since you don't know the columns ahead of time) the proc may not actually generate the same columns with FMTONLY ON so it *MAY* be necessary to add the line SET FMTONLY OFF to the proc.  Of course this means that the proc will fully execute twice every time you call it with the above code which will be slow.  Also, it would be best if the proc performed only reads, not updates otherwise things will get updated twice, and if the update from the first run effects the selects from the second run... well it's already messy, why go there?

2.  the line drop table ##'+@UniqueTempName+'
assumes that you intend to use the metadata provided to build your own table and use it to store the results of executing the proc yet again.  That's fairily redundant, since you already have the results in a temp table whose name you know.

3.  Generally speaking whenever you find yourself jumping through hoops to accomplish something, it's a good time to look at your architecture:  Is it really necessary to capture the results of a proc who's columns cannot be known at design time to a temp table?  When other people have had to solve similar problems have they requred this?  Is this code going to be easily understood and maintained?
0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 800 total points
ID: 12061666
May be it help you.
As I understand that 'john,mary,joe,john' = 'john,mary,joe'


Declare @S varchar(1000)
Declare @A varchar(1000)
Declare @I int
Declare @C int
create table #temp ( id int, val varchar(100) )
declare @ttt table (id int identity(1,1),MN varchar(100))
set @S= 'john,mary,joe,john'

SET @C=0
SET @I=1
IF LEN(@S)>0
 BEGIN
  IF SUBSTRING(@S,LEN(@S),1)<>',' SET @S=@S+','
  WHILE @I<>0
    BEGIN
      SET @I=CHARINDEX(',',@S,@C+1)
      IF @I>0 INSERT INTO @TTT(MN) VALUES(SUBSTRING(@S,@C+1,@I-@C-1))
      SET @C=@I
    END
 END

SELECT @C=COUNT(*)FROM (select DISTINCT MN from @ttt) thn
SELECT @C

IF @C<>0
  SET @I=1
  WHILE @I<=@C
    BEGIN
      SET @A='ALTER TABLE #TEMP ADD VAL_'+LTRIM(STR(@I))+' varchar(100)'
      EXEC(@A)
      SET @I=@I+1
    END

insert into #temp exec myproc 2, 3,@S
0
 
LVL 6

Expert Comment

by:OlegP
ID: 12061686
delete 'SELECT @C' (test info)
0
 
LVL 12

Author Comment

by:ill
ID: 12072166
thx all for comments and answers.
@OleqP
your code works, if one can guess the result. also column names are known in #temp table for calculation

@BoyPupa
great. universal solution. i always keep forgetting to link (local).

---
keep coding :)
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!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

597 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