Use the global temporary table of sqlserver to prevent repeated 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 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 -- 2001/10/26 21:36 zhuzhichao in Nanjing

/* 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, although the global temporary table still exists in tempdb, it is 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, we useProgramWhen this process is called, 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 !"

(Test environment: SERVER: winnt Server 4.0 SQL Server7.0 Workstation: winnt workstation)

Author: zhuzhichao Source: Internet