Insert into Exec

Question

Post reply

Insert into Exec

vmssanthosh

SSC Eights!

Points: 806
More actions
April 23, 2010 at 4:27 am

#220793

Hi
We are getting the following error when trying to insert in to a temp table using “Insert into exec proc”.
Actually as per the statements the error should have got in any of the
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
On Examining the issue we found that error happens only if the dataset procedure has the try catch in it.
Please advise us for workarounds if any.
create proc stp_datasetwithtrycatch
as
begin
begin try
select 1,2
end try
begin catch
print 'error'
end catch
end
go
create proc stp_invoke
as
begin try
create table #temp
(a int,b int)
insert into #temp (a)
exec stp_datasetwithtrycatch
end try
begin catch
print 'error here'
end catch
go
exec stp_invoke
drop proc stp_datasetwithtrycatch
drop proc stp_invoke

Viewing 4 posts - 1 through 3 (of 3 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

vmssanthosh (4/23/2010)
Please advise us for workarounds if any

Don't specify a column list on the INSERT statement:

insert into #temp --(a)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vmssanthosh SSC Eights! Points: 806 More actions · Answer 2

hi

Thanks for your reply. but we cannot do that because we do have more columns for some other purpose and we cannot expect that proc to return all those columns.. since that particular proc is in different module, for which we do not have control. and the calle procedure also for another module.. i am working on integration of two different modules.. there i stucks..

if you need more information i will give you..

Regards

VMSSanthosh

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

I believe that OPENROWSET may solve the problem you're having.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions