Sql-server – Can two sessions create #temp tables with the same name

cursorssql server

I am creating a temporary table (#myTable) and using a cursor. Does this create a problem when concurrent users are accessing the cursor through my application? Does it allow me to create separate temp tables with the same name?

Following is the sample code:

Open cursor;
Fetch Next from cursor into @Variable_Temp_Table_Name;
Create table #myTable(pk int)
While @@Fetch_Status = 0
Begin    
Fetch Next from cursor into @Variable_Temp_Table_Name;
End

Best Answer

SQL server always append some random number in the end of a temp table name (behind the scenes), when the concurrent users create temp tables in their sessions with the same name, sql server will create multiple temp tables in the tempdb.

I created 3 temp tables called #TempTable in three different sessions in my SSMS, now if I go to the tempdb I can see the temp tables created there with a random (unique) string appended to each temp table's name.

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Sql-server – Modifying a result set in a Stored Procedure, then returning that modified set

Is there a better way of achieving this, or am I on the right track?

Yes there is and no, I don't think you are on the right track. There is almost no reason to use a cursor in this situation.

If ShowLocation is a value on each record in the table then this is the kind of situation where a CASE statement comes in handy.

SQL Server code would be similar to the following

CREATE PROCEDURE dbo.GetLocationData
AS
SELECT
  <all_other_fields_here>,
  CASE 
     WHEN ShowLocation = 1 THEN Location 
     ELSE NULL 
END AS Location
FROM PERMTABLE
WHERE Location within bounds -- not entirely sure how you're doing this
ORDER BY Location;
GO

Best Answer

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

Sql-server – Modifying a result set in a Stored Procedure, then returning that modified set

Related Question