When we develop business software, you will often encounter such a problem: How to prevent users from repeatedly log in to our system? Especially for banks or financial sectors, it is to restrict users to log in multiple times with their work marks.
Some people may say that when the user information table is added to determine the status of the user working number login, log in and write 1, write 0 when exiting, and determine if its flag is 1, if so, the user working number is logged in. . But this is bound to bring new problems: If there is an unpredictable phenomenon such as power failure, the system is not properly exiting, and the logo location cannot be 0, then the next time the subscriber number is logged in, it is not available. How to do it?
Maybe we can change your ideas: What can I be automatically recovered after the Connection is disconnected? Yes, SQL Server's temporary table has this feature! But this situation we cannot use to use local temporary tables, because local temporary tables are a separate object for each Connection, so we can only use global temporary tables to achieve our goal.
Ok, the situation is already clear, we can write a simple stored procedure below:
Create Procedure GP_FINDTEMPTABLE - 2001/10/26 21:36 zhuzhichao in nanjing
/ * Looking for global temporary table named after operating staff
* If you don't have to set the OUT parameter to 0 and create the table, if there is, set the OUT parameter to 1
* After the Connection is disconnected, the global temporary table is automatically recycled by SQL Server
* If an accident, the overall temporary table is still present in Tempdb, but has lost activity
* If you use the Object_ID function to judge it, it will not exist.
* /
@v_userid varchar (6), - Operating employee number
@i_out int out - Output parameter 0: No login 1: Listen to
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
During this process, we see if the process will create one with a global temporary table named after the user's class number, and set the OUT parameter to 0, if it already exists, set the OUT parameter to 1.
In this way, when we call this process in our app, if the obtained OUT parameter is 1, we can jump out a message telling the user to say "Sorry, this work number is being used!"
(Test Environment: Server: Winnt Server 4.0 SQL Server7.0 Workstation: Winnt Workstation)
