Solved

insert into #temp exec

Posted on 2004-09-14
9
9,772 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 12

Author Comment

by:ill
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
delete 'SELECT @C' (test info)
0
 
LVL 12

Author Comment

by:ill
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now