Insert result set of sp into a temp table

Ajay Mavatkar

9y1.8k

Hi,

I need to insert result set of SP into a temp table, however it is giving the following error.

'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction'

Please find my effort regarding this.

DECLARE @tempTable TABLE(ID INT, [Description] NVARCHAR(50));

INSERT @tempTable EXEC sp_MyTable] 100

select * from @tempTable

Any help would be apprciated.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar9y ago
Can you post your procedure?
0

Sahil Saini9y ago

Then better you convert your SP into table valued function and get the data out from that function to insert.

CREATE FUNCTION FunctionName (@ID varchar(30)) 
RETURNS TABLE AS 
RETURN (SELECT t1.Field1, t2.Field2         
FROM   tableName t1         
JOIN   tablename2 t2 ON t1.title_id = t2.title_id         
WHERE  t2.ID = @ID)

This function will return Field1 and Field2 by passing @ID as parameter to function.

Call this in SP like -->

INSERT INTO #YOUR_TEMP_TABLE SELECT * FROM FunctionName (12)

Ajay Mavatkar9y ago
Hi,

Am getting 'Cannot use the ROLLBACK statement within an INSERT-EXEC statement.'
error further on adding the above eg.
0

Sahil Saini9y ago

Check the XACT_STATE() in a CATCH block

BEGIN TRY

Write sql queries here 

END TRY
BEGIN CATCH
Declare @error int, @message varchar(4000), @xstate int; 
select @error = ERROR_NUMBER(),
       @message = ERROR_MESSAGE(), 
       @xstate = XACT_STATE(); 
if @xstate = -1 
   rollback; 
if @xstate = 1 and @trancount = 0 
   rollback ;
if @xstate = 1 and @trancount > 0 
   rollback transaction procedure_name; 
raiserror ('procedure_name: %d: %s', 16, 1, @error, @message) ; 
end catch