Use SQL Server's global temporary table to prevent users from repeatedly logging on

server| Temporary Table | repeat
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, but also to restrict the user as its work number of multiple login.

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 after the connection is disconnected? Yes, SQL Server's temp table has this feature! But we can't use a local temp table here, because a local temp table is a separate object for each connection, so we can only use the global temp 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--2001/10/26 21:36 Zhuzhichao in Nanjing

/* 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, although still present in tempdb, has lost its 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!" ”

(Test environment: Server: WINNT Server 4.0 SQL Server7.0 workstation: WINNT workstation)