Simple Stored Procedure

Tag: CTI ar code time using OS Io data

1. Create a stored procedure

Like a data table, we need to create a stored procedure before using it. Its concise syntax is:

Quote:

Create proc stored procedure name

[Parameter list (multiple parameters are separated by commas (,)]

As

SQL statement

Example:
Quote:

Create proc upgetusername

@ Intuserid int,

@ Ostrusername nvarchar (20) Output
-- Parameters to be output

As

Begin

-- Assign the uname value to the @ ostrusername variable, that is, the parameter to be output.

Select @ ostrusername = uname from
Uuser where [email protected]

End

The create proc statement (the complete statement is create procedure) tells SQL server that a stored procedure needs to be created now. upgetusername is the name of the stored procedure, @ intuserid and @ ostrusername are the two parameters of the stored procedure respectively. Note that in SQL Server, all User-Defined variables start, the output keyword indicates that this parameter is used for output. As is followed by the Stored Procedure content. If you run the preceding code once in the "query analyzer", SQL Server creates a stored procedure named "upgetusername" in the current database. You can open "Enterprise Manager", select the database for the current operation, and select "Stored Procedure" in the tree list on the left ", now you can see the stored procedure you just created in the list on the right (if not, refresh it ).

Ii. Call of Stored Procedures

Previously, we have created a stored procedure named "upgetusername". The function of this stored procedure is to obtain the name of a user. After the storage process is set up, the next step is to call it in the application. Next, let's take a look at the call in the ASP program.

Quote:
Dim adocomm

'// Create an object for calling the Stored Procedure

Set adocomm = Createobject ("ADODB. Command ")

With adocomm

'// Set the connection and set adoconn to the connected ADODB. connection object

. Activeconnection = adoconn

'// The type is stored in the process, admo-storedproc = 4

. Commandtype = 4

'// Stored procedure name

. Commandtext =
"Upgetusername"

'// Set the user ID
. Parameters. Item ("@ intuserid"). value = 1

'// Execute the Stored Procedure

. Execute

'// Get the name of the user returned from the Stored Procedure

Response. Write "User name :"&
. Parameters. Item ("@ ostrusername"). Value

End

'// Release the object

Set adocomm = nothing


Through the above two steps, we can create and use simple stored procedures. Next we will look at a slightly more complex stored procedure to further understand the application of the stored procedure.


Iii. Practical Application of Stored Procedures

User Login is often used in ASP projects. I believe many of my friends have done similar systems, but there may not be many friends who use stored procedures for verification. We will use it as an example, write a simple Stored Procedure for user login verification.

Quote:

Create proc upuserlogin

@ Strloginname nvarchar (20 ),

@ Strloginpwd nvarchar (20 ),

@ Blnreturn bit output

As

-- Defines a variable temporarily used to save the password

Declare @ strpwd nvarchar (20)

Begin

-- Query the password of the current user from the table and assign the value to the @ strpwd variable. We will compare it below

Select @ strpwd = uloginpwd from
Uuser where [email protected]

If @ strloginpwd = @ strpwd

Begin

Set @ blnreturn =
1

-- Updates the last logon time of a user.

Update uuser set
Ulastlogin = getdate () Where [email protected]

End

Else

Set @ blnreturn = 0

End

The stored procedure for user login has been set up. Now let's try it in the program. NOTE: If multiple statements exist in a region, you must use the begin... end keyword.

Quote:

Dim adocomm

'// Create an object for calling the Stored Procedure

Set adocomm = Createobject ("ADODB. Command ")

With adocomm

'// Set the connection and set adoconn to the connected ADODB. connection object

. Activeconnection = adoconn

'// The type is stored in the process, admo-storedproc = 4

. Commandtype = 4

'// Stored procedure name

. Commandtext =
"Upuserlogin"

'// Set the logon name


. Parameters. Item ("@ strloginname"). value = "admin"

'// Set the logon Password


. Parameters. Item ("@ strloginpwd"). value = "123456"

'// Execute the Stored Procedure

. Execute

'// Determine whether the logon is successful

If
. Parameters. Item ("@ blnreturn"). value = 1 then

Response. Write "Congratulations! login successful! "

Else

Response. Write "no, it seems wrong... "

End if

End

'// Release the object

Set adocomm = nothing

Through the above steps, the simple user login verification process is also completed. Now, you only need to integrate it into the program to implement simple user login verification, you can handle other details by yourself.

The two stored procedures described above return only one value. Let's look at a stored procedure that returns a record set.

Quote:

Create proc upgetuserinfos

@ Intusergroup int

As

Begin

-- Extract qualified data from the database

Select uname, ugroup, ulastlogin
From uuser where [email protected]

-- Insert a column total

Union

Select 'total count: ', count (ugroup), null from
Uuser where [email protected]

End

Now let's take a look at ASP program calls.

Quote:

Dim adocomm

Dim adort

'// Create an object for calling the Stored Procedure

Set adocomm = Createobject ("ADODB. Command ")

Set adors = Createobject ("ADODB. recordset ")

With adocomm

'// Set the connection and set adoconn to the connected ADODB. connection object

. Activeconnection = adoconn

'// The type is stored in the process, admo-storedproc = 4

. Commandtype = 4

'// Stored procedure name

. Commandtext =
"Upgetuserinfos"

'// Set the user group


. Parameters. Item ("@ intusergroup"). value = 1

'// Execute the stored procedure. Unlike the preceding examples, the recordset open method is used here.

Adors. Open adocomm

'// Display the first value

Response. Write

Adors. Fields (0). Value

End

'// Release the object

Set adors = nothing

Set adocomm = nothing