Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9824
  • Last Modified:

insert into #temp exec

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
ill
Asked:
ill
2 Solutions
 
OlegPCommented:
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
 
vidnan123Commented:
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
 
illAuthor Commented:
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
Independent Software Vendors: 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!

 
HilaireCommented:
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
 
illAuthor Commented:
@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
 
BoyPupaCommented:
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
 
OlegPCommented:
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
 
OlegPCommented:
delete 'SELECT @C' (test info)
0
 
illAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now