Using SQL Server temporary tables skillfully

SQL Server temp tables are often needed, and here's how you can use SQL Server temp tables to troubleshoot problems that prevent users from recurring logins for your information.

When we develop business software, we often encounter the question of how to prevent users from repeatedly logging on to our system. Especially for the bank or the financial department, it is to restrict the user to log in as their work number.

Some people may say that in the User Information table, add a field to determine the status of user work number login, write 1 after login, write 0 when you exit, and when you log in to determine whether the sign is 1, if you do not let the user work number login. But this is bound to bring new problems: such as a power outage, such as unpredictable phenomenon, the system is abnormal exit, can not be the flag location of 0, then the next time the user work number login can not log in, this How to do?

Maybe we can change the train of thought: Is there anything that can be automatically recycled by the system after the connection is disconnected? By the way, SQL Server Temp table has this feature! But we can't use local SQL Server temp tables here. Because a local temporary table is a separate object for each connection, we can only use the global temporary table to achieve our goal.

Well, as the situation is clear, we can write a simple stored procedure like the following:

 
 

  
  

  
CREATE PROCEDURE Gp_findtemptable
  
  

  

  
  

  
/* Look for a global temporary table named with the operation employee number
  
  

  
* If not, place the out parameter to 0 and create the table, and if so, set the out parameter to 1
  
  

  
* The global temporary table will be automatically recycled by SQL Server after the connection is disconnected
  
  

  
* In the event of a power outage, the global temporary table exists in tempdb,
  
  

  
But they've lost their activity.
  
  

  
* Use the OBJECT_ID function to judge that it does not exist.
  
  

  
*/
  
  

  
@v_userid varchar (6),--Operation employee number
  
  

  
@i_out int out--output parameter 0: No login 1: Already logged in
  
  

  
As
  
  

  
DECLARE @v_sql varchar (100)
  
  

  
If object_id (' tempdb.dbo.## ' "' + @v_userid) is null
  
  

  
Begin
  
  

  
Set @v_sql = ' CREATE TABLE # # ' + @v_userid +
  
  

  
"(UserID varchar (6)) '"
  
  

  
EXEC (@v_sql)
  
  

  
Set @i_out = 0
  
  

  
End
  
  

  
Else
  
  

  
Set @i_out = 1
 
 

In this process, we see that if a global temporary table named with a user name does not exist, the procedure creates one and puts the out parameter to 0, and if it already exists, the out parameter is set to 1.

So, when we call this procedure in our application, if we get an out parameter of 1 o'clock, we can just jump out of a message and tell the user, "Sorry, this work is being used!"