Return data from exec statement

Question

Post reply

Return data from exec statement

Tim Coker

SSC Eights!

Points: 825
More actions
June 14, 2005 at 1:09 pm

#186263

I'm running a query against a Linked Server (AS400/DB2) using the openquery function. I need to pass in a date parameter, so I have to use the method described here. At the end of the script, I run an exec statement, and I get data in the bottom of query analyzer. My problem is I need to do further processing with the records returned, and cant quite figure out how to get the exec's return in a form I can use.
I've tried declaring a cursor to use the returns like this:
declare test cursor for
exec <SP>
<SP> being the script mentioned above, but I get a script error from QA...
I think I just don't quite understand whats going on w/ an exec's return. Can anyone clarify what exactly the return from the exec is?
Here's the code from the stored procedure whose resultset I need to process further (with the select statement changed to protect the innocent...):
create procedure SP
as
Begin
declare @SQL as varchar(1000)
declare @LinkedServer as varchar(30)
declare @Statement as varchar(1000)
declare @Date as datetime
set @LinkedServer = 'AS400'
set @Date = dateadd(dd,-2,getdate())

set @SQL = 'select * from table where date = ' + @Date

set @Statement = 'select * from OPENQUERY(' + @LinkedServer + ', '
set @Statement = @Statement + '''' + @SQL + ''')'

exec(@Statement)
End
Like I said earlier, when run from query analyzer, I see what I want, but can't figure out how to process the results further.
Thanks in advance for any help!
Tim C
(PS, before anyone asks, this query I have to do HAS to be run through an openquery function. if I just run the query adhoc, it can take 20 mins or more to complete. the underlying table is HUGE....)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Create a temp table with the same structure as the results then do this :

Insert into #temp (col1, col2, id) exec(@Statement)

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

If you know the data types of the data being returned from the dynamic sql you can insert the results of the exec in a temp table. Create the temp table first and then do:

insert #temptable (column_list) exec (@whatever)

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

June 14, 2005 at 1:22 pm

#565465

Thanx for confirming .

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

I have to learn to type faster so I can get there first next time.

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

You only have 2 minutes to cut down... shouldn't be too hard .

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

I don't know. I've been at this for 20 years and I still just use 2 fingers. It seems I can't type any faster than I think.

And then again, I might be wrong ...
David Webb

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Now if it's not a typing problem, I can't help you, otherwise there's always those typing teaching softwares .

Tim Coker SSC Eights! Points: 825 More actions · Answer 8

Haven't used temp tables before, so this is probably a very basic question ... What's the lifespan of the table? Length of the current connection? Some predefined timeout?

David Webb-CDS SSCoach Points: 17398 More actions · Answer 9

Length of the connection or until you explicitly drop it.

And then again, I might be wrong ...
David Webb

Marry Krissmess SSC Veteran Points: 262 More actions · Answer 10

...and it is placed in tempdb

just to mention that it should be huge enough to hold the result without problems