3

I have the following stored proc which uses a temp table to bulk import the data. I understand the temp tables are unique for every session, however i'm wondering if my application uses threads and makes multiple concurrent request to the stored proc, using the same sql connection from the application pool, will they end up referencing the same temp table?

CREATE PROCEDURE [dbo].[Mytestproc]
AS
  BEGIN
      BEGIN TRANSACTION

      CREATE TABLE #Hold
        (
           ID INT,
           VAL NVARCHAR(255)
        )

      BULK INSERT #Hold
        FROM 'C:\data.txt'
        WITH
          (
            FieldTermInAtOr ='|',
            RowTermInAtOr ='\n'
          )

      SELECT *
      FROM   #Hold

      DROP TABLE #Hold

      COMMIT TRANSACTION
  END
newbie
  • 1,485
  • 2
  • 18
  • 43

2 Answers2

3

Whilst one thread is using a connection and executing this stored procedure, that same connection cannot be reused by the connection pool - so there's no danger of sharing there. Other threads cannot use this connection, and will open new ones instead.

In addition, there's no need to drop the temp table before the stored procedure ends - temp tables created within a stored proc are dropped automatically when the proc is exited.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    I thought with Multiple Active Result Sets (MARS) you could make multiple request over the same connection, maybe read only? – newbie Oct 19 '11 at 18:40
  • @newbie - MARS only allows multiple selects to run in parallel, and you'd have to be explicitly sharing the connection object in your code. – Damien_The_Unbeliever Oct 19 '11 at 18:43
  • I think this sums it up - "Whilst one thread is using a connection and executing this stored procedure, that same connection cannot be reused by the connection pool" – newbie Oct 20 '11 at 20:16
0

I would think that if your application is making concurrent calls to this stored procedure, it would be separate connections, in which case they would be separate temp tables.

Best way to find out is to test it, though. Have your application make concurrent calls, and to hold these connections. Then execute sp_who to see if connections are multiple for your application, and view the output of these temp tables (given that they contain different data).