Application of stored procedures in SQL Server database development

Server| stored Procedure | data | database

Many friends may have been using SQL Server for development for some time, but have not or rarely used the stored procedures in the project, perhaps some friends think that there is no need to use stored procedures and so on. In fact, when you finish the maintenance phase of a project, you will find that the stored procedures have brought us benefits, easy to modify, can not change our application, only need to change the contents of the stored procedures, but also can make our program speed improved.

Reference:
Definition in SQL SERVER Books Online:
A stored procedure is a collection of Transact-SQL statements that are saved to accept and return user-supplied parameters.
You can create a procedure for permanent use, or for temporary use in a session (local temporary procedures), or for temporary use in all sessions (global temporary procedures).
You can also create stored procedures that run automatically when Microsoft SQL Server starts.

To use a stored procedure, first we must familiarize ourselves with some basic T-SQL statements, because the stored procedure is made up of a set of T-SQL statements, and we need to understand some concepts about functions, procedures, because we need to call the stored procedure in the application, just as we call the application's function, However, there are some differences in the methods invoked.
Let's take a look at how the stored procedures are built and used.
First, create a stored procedure
Like a datasheet, we need to create a stored procedure before using it, and its concise syntax is:

Reference:
CREATE PROC Stored Procedure name
[Parameter list (multiple to "," delimited)]
As
SQL statement

Cases:

Reference:
CREATE PROC Upgetusername
@intUserId INT,
@ostrUserName NVARCHAR Output--parameters to be exported
As
BEGIN
--Assign the value of the uname to @ostrUserName variable, which is the parameter to output
SELECT @ostrUserName =uname from Uuser WHERE uid= @intUserId
End

Where the Create PROC statement (the full statement is created PROCEDURE) means telling SQL SERVER that you now need to establish a stored procedure, upgetusername is the stored procedure name, @intUserId, and @ Ostrusername is the two parameters of the stored procedure, note that in SQL Server, all user-defined variables begin with "@", and the OUTPUT keyword indicates that the parameter is used for outputting, and the following is the stored procedure content. As soon as the above code is executed in Query Analyzer, SQL Server creates a stored procedure named "Upgetusername" in the current database. You can open "Enterprise Manager", select the database of the current operation, and then select "Stored Procedures" in the tree list on the left, where you can see the stored procedure you just created in the list on the right (if not, refresh it).
Second, the call of the stored procedure
We have previously created a stored procedure called "upgetusername", which literally understands the function of the stored procedure to obtain the name of a particular user. The stored procedure is set up, and the next step is to call it in the application, and look at the call in the ASP program.

Reference:
Dim Adocomm
'//Create an object that we use to invoke the stored procedure
Set Adocomm = CreateObject ("Adodb.command")
With Adocomm
'//Set connection, set Adoconn as already connected ADODB. Connection objects
. ActiveConnection = Adoconn
'//type is stored procedure, adCmdStoredProc = 4
. CommandType = 4
'//Stored procedure name
. CommandText = "Upgetusername"
'//Set user number
. Parameters.item ("@intUserId"). Value = 1
'//Execute stored Procedure
. Execute
'//Get the name of the user returned from the stored procedure
Response.Write "User name:" &. Parameters.item ("@ostrUserName"). Value
End With
'//Release object
Set Adocomm = Nothing

In both of these steps, we can already create and use simple stored procedures. Let's look at a slightly more complex stored procedure to learn more about the application of the stored procedure.
Third, the actual application of the stored procedure
User login in the ASP project is often used, I believe many friends have done similar systems, but the use of stored procedures to do the validation of friends may not be many, then we do it as an example, write a simple user logon verification of the stored procedures.

Reference:
CREATE PROC Upuserlogin
@strLoginName NVARCHAR (20),
@strLoginPwd NVARCHAR (20),
@blnReturn BIT OUTPUT
As
--Defines a variable that is temporarily used to save a password
DECLARE @strPwd NVARCHAR (20)
BEGIN
--Query The current user's password from the table, assign the value to @strPwd variable, and compare him below
SELECT @strPwd =uloginpwd from Uuser WHERE uloginname= @strLoginName
IF @strLoginPwd = @strPwd
BEGIN
SET @blnReturn = 1
--Update user last logon time
UPDATE uuser SET ulastlogin=getdate () WHERE uloginname= @strLoginName
End
ELSE
SET @blnReturn = 0
End

The stored procedure that the user logs in is OK, now try it in the program. Note that if you have more than one statement in a region, you must use the BEGIN ... End keyword.

Reference:
Dim Adocomm
'//Create an object that we use to invoke the stored procedure
Set Adocomm = CreateObject ("Adodb.command")
With Adocomm
'//Set connection, set Adoconn as already connected ADODB. Connection objects
. ActiveConnection = Adoconn
'//type is stored procedure, adCmdStoredProc = 4
. CommandType = 4
'//Stored procedure name
. CommandText = "Upuserlogin"
'//Set login name
. Parameters.item ("@strLoginName"). Value = "Admin"
'//Set login password
. Parameters.item ("@strLoginPwd"). Value = "123456"
'//Execute stored Procedure
. Execute
'//Determine if login is successful
If. Parameters.item ("@blnReturn"). Value = 1 Then
Response.Write "Congratulations, login success!" "
Else
Response.Write "No, it seems wrong" oh ... "
End If
End With
'//Release object
Set Adocomm = Nothing

Through the above steps, the simple user logon verification process is done, now as long as the integration into the program can be implemented simple user login verification, about the other details on your own to deal with.
The two stored procedures described above all return only one value, and here we look at a stored procedure that returns a recordset.

Reference:
CREATE PROC Upgetuserinfos
@intUserGroup INT
As
BEGIN
--Extract eligible data from the database
SELECT uname,ugroup,ulastlogin from Uuser WHERE ugroup= @intUserGroup
--Insert a column total
UNION
SELECT ' Total number: ', COUNT (Ugroup), NULL from Uuser WHERE ugroup= @intUserGroup
End

Now let's look at the invocation of the ASP program.

Reference:
Dim Adocomm
Dim Adort
'//Create an object that we use to invoke the stored procedure
Set Adocomm = CreateObject ("Adodb.command")
Set adors = CreateObject ("ADODB. Recordset ")
With Adocomm
'//Set connection, set Adoconn as already connected ADODB. Connection objects
. ActiveConnection = Adoconn
'//type is stored procedure, adCmdStoredProc = 4
. CommandType = 4
'//Stored procedure name
. CommandText = "Upgetuserinfos"
'//Set user group
. Parameters.item ("@intUserGroup"). Value = 1
'//execute stored procedures, unlike the ones above, where the recordset's Open method is used
Adors.open Adocomm
'//Display first value
Response.Write Adors.fields (0). Value
End With
'//Release object
Set adors = Nothing
Set Adocomm = Nothing

How, it's also simple, but the use of stored procedures is more than just that, he has more powerful features, such as using cursors, temporary tables to invoke data from multiple tables, or even multiple databases, and then return to the user, which you can explore slowly in the process.
Well, the stored procedure application is first introduced here, if you have any questions about this article, welcome to submit, I will try to give answers. Next time I'll give you an introduction to the application of triggers.