Clever Use of SQL Server temporary tables

SQL Server temporary tables are frequently used. The following describes how to use SQL Server temporary tables to prevent repeated user logon.

When developing business software, we often encounter the following problem: how to prevent users from repeatedly logging on to our system? Especially for banks or financial departments, it is necessary to restrict users to log on multiple times as their employee IDs.

Some people may say that they should add a field in the user information table to determine the logon status of the user's employee ID, write 1 after logon, write 0 when exiting, and determine whether the flag is 1 when logon, if yes, the user's employee ID is not allowed to log on. However, this will inevitably lead to new problems: in the case of unpredictable phenomena such as power outages, the system exits abnormally and cannot set the mark position to 0, so next time you log on with this user's employee ID, you cannot log on. What should I do?

Maybe we can change our thinking: What can be automatically recycled by the system after the connection is disconnected? By the way, SQL Server temporary tables have this feature! However, in this case, we cannot use a local SQL Server temporary table. Because a local temporary table is an independent object for each connection, we can only use a global temporary table for our purpose.

Now, the situation is clear. We can write a simple stored procedure like the following:

Create procedure gp_findtemptable

/* Search for a global temporary table named after the operator's employee ID
* If not, set the out parameter to 0 and create the table. If yes, set the out parameter to 1.
* After the connection is disconnected, the global temporary table is automatically reclaimed by SQL Server.
* In the event of an accident such as power failure, the global temporary table still exists in tempdb,
But no longer active
* When the object_id function is used to determine whether the object does not exist.
*/
@ V_userid varchar (6), -- operator ID
@ I _out int out -- output parameter 0: No Logon; 1: logged on
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 can see that if the global temporary table named after the user's employee number does not exist, a new table will be created and the out parameter is set to 0, if it already exists, set the out parameter to 1.

In this way, when we call this process in our application, if the out parameter obtained is 1, we can jump out of a message and tell the user to say "sorry, this employee ID is in use!"