Insert result set of sp into a temp table

Question

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.

Manish Kumar · Answer

Can you post your procedure?

Sahil Saini · Answer

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 Mavatkar · Answer

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

Sahil Saini · Answer

Check the XACT_STATE() in a CATCH block BEGIN TRY Write sql queries here END TRY BEGIN CATCH D eclare @ 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