must be drop table ?

• Alessandro Andreatta

  SSC-Addicted

  Points: 406

  More actions

  March 26, 2021 at 10:43 am

  #3862351

  Hello, i'm an Italian developer (excuse me for my english).

  I have a Store Procedure like this

  BEGIN TRY
  BEGIN TRANSACTION myTransaction
  
  DECLARE @TempMrc TABLE (idServizio int, idRgp int, Salita bit, Data Date)
  DECLARE @TempReport TABLE (Data Date, Salita smallint, Discesa smallint)
  
  DECLARE @crs CURSOR
  SET @crs = CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  ....
  
  OPEN @crs
  FETCH @crs INTO @idServizioCursore, @DataMarcataCursore, @idRgpCursore, @Salita
  WHILE @@FETCH_STATUS = 0
  
  ...
  // some operation insert update of my temps table @TempMrc, @TempReport
  
  FETCH NEXT FROM @crs INTO @idServizioCursore, @DataMarcataCursore, @idRgpCursore, @Salita
  END
  CLOSE @crs
  DEALLOCATE @crs
  
  set @myresult
  
  COMMIT TRANSACTION myTransaction

  after set @myresult and before commit transaction

  i must drop temp table or not ?

  probably it will be a high use function, working with temporary tables it is advisable to change the transaction level (serializable) ?

  thanks

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  March 26, 2021 at 12:43 pm

  #3862401

  No need to drop the table. It'll get cleaned up by internal processes once that procedure completes.

  Now, we could talk about using a cursor to insert rows into a table. That's a pretty inefficient way to run queries. You'd be much better of using INSERT... SELECT.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Alessandro Andreatta

  SSC-Addicted

  Points: 406

  More actions

  March 26, 2021 at 12:53 pm

  #3862403

  Thanks Grant, i know the problems with using the cursors. It was my last choice.

  Maybe in a next post I will explain the problem of obtaining data via query.

  thanks

   

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  March 26, 2021 at 3:12 pm

  #3862439

  // some operation insert update of my temps table @TempMrc, @TempReport

  If you are only doing DML operations on the @Tables, then you should not be using a transaction, as the @Tables do not participate in transactions.

  DECLARE @TempMrc table ( idServizio int, idRgp int, Salita bit, Data date );
  
  BEGIN TRANSACTION;
  
  INSERT INTO @TempMrc ( idServizio, idRgp, Salita, Data )
  VALUES ( 1, 1, 0, GETDATE() );
  
  ROLLBACK TRANSACTION;
  
  SELECT * FROM @TempMrc;

  ---

  How to post data/code on a forum to get the best help.