Solved

insert into #temp exec

Posted on 2004-09-14
9
9,786 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

789 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