Calling stored procedure and save its results in a temporary table (error)

Question

Trying to call a procedure and insert the results into a temp table:

CREATE TABLE #TempTable22
(
   ChargeType             Int,
   ChargeCode             varchar(250),
   CarrierCode            varchar(250),
   Market                 varchar(250),
   CurrencyCode           varchar(250),
   PaymentMethodCode      varchar(250),
   ForiegnCurrencyCode    varchar(250),
   ChargeAmount           Float,
   ForiengAmount          Float,
   MarketCharge           varchar(250),
   DirectRate             Float,
   ExcessBagCommisionRate Float

)

INSERT INT #TempTable22
    EXEC [dbo].[Sp_AutoJV_SalesSummary_ForTest]  @DateFrom, @DateTo, @CurrencyCode, @OrganizationCode, @OrgCurrencyCode, @LocationCode, @Market, @Sales, @PaymentMethodCode;


SELECT 
   ChargeType,            
   ChargeCode,           
   CarrierCode,           
   Market,                
   CurrencyCode,          
   PaymentMethodCode,  
   ForiegnCurrencyCode,  
   ChargeAmount, 
   ForiengAmount,     
   MarketCharge,     
   DirectRate,      
   ExcessBagCommisionRate,
   0 * ExcessBagCommisionRate as Test 
FROM
    #TempTable22

Note: # when I call the stored procedure without inserting it there is no problem but when I add the insert into #TempTable22 the error will now prompt.

Full error

Msg 3930, Level 16, State 1, Procedure Sp_AutoJV_SalesSummary_GetChargeDetailsNoDGV2_ForTest, Line 46 [Batch Start Line 73]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 208, Level 16, State 0, Procedure Sp_AutoJV_SalesSummary_GetChargeDetailsNoDGV2_ForTest, Line 835 [Batch Start Line 73]
Invalid object name '#Stations'.

Msg 3930, Level 16, State 1, Procedure Sp_AutoJV_SalesSummary_Report_ForTest2, Line 63 [Batch Start Line 73]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (0 row(s) affected)

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jan 11 '19 at 05:16

score 0 · Answer 1 · answered Jan 11 '19 at 02:34

0

You need to create Table valued function to be able to use it in an select or insert statement. Please see below link

When would you use a table-valued function?

answered Jan 11 '19 at 02:34

Ashutosh Arya

1,138
2
8
14

Calling stored procedure and save its results in a temporary table (error)

1 Answers1