Solved

insert into #temp exec

Posted on 2004-09-14
9
9,783 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 300 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 200 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

770 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