SQL Server Stored Procedure

The stored procedure is used in many places when the data center is reconstructed. Here is a brief summary.

Stored Procedure is a set of SQL statements for specific functions. It is compiled and Stored in the database. You can run a stored procedure by specifying the name and parameters (if the stored procedure has parameters. By moving data processing programs from customer applications to servers, stored procedures can reduce network traffic and improve performance and data integrity.

1. Create

There are two main methods:

(1)Create a stored procedure using SQL Server Management Studio

① Click "+" in front of the database, and then click "programmable", find "Stored Procedure", and right-click "create stored procedure". The system automatically generates a template, you only need to modify the corresponding method.

② Create a stored procedure using T-SQL

Create procedure process name @ Param parameter type ...... @ Param parameter type output ...... ASBEGIN command line or command block END

For example, create a stored procedure for adding a user:

CREATE procedure [dbo].[pro_AddUser]@user_id varchar(11),@user_name varchar(11),@user_level varchar(10),@user_pwd varchar(11),@user_register varchar(10)asinsert T_User values (@user_id ,@user_name,@user_level ,@user_pwd ,@user_register )

Note the following points:

* Process name: Start With proc _ or proc _ whenever possible. We do not recommend that you use sp _ as the prefix, because the stored procedures of SQL Server System start with sp, in this way, the system itself will be searched first to reduce the query speed.

* The statements between BEGIN... END... are mainly tasks to be executed in the stored procedure:

Query statement: SELECT

INSERT statement: INSERT

UPDATE statement: UPDATE

DELETE statement: DELETE

If the stored procedure returns an OUTPUT parameter or 0, the OUTPUT

2. Call

(1) Call the stored procedure using T-SQL

EXECUTE Process name [parameter value,...]

(2) On the client code side

Layer D code:

    Public Function Add_User(enuser As Entity.E_User) As Boolean Implements IUser.Add_User        Dim pt As SqlParameter() = {New SqlParameter("@user_id", enuser.UserID), _                                    New SqlParameter("@user_name", enuser.UserName), _                                    New SqlParameter("@user_level", enuser.Level), _                                    New SqlParameter("@user_pwd", enuser.UserPwd), _                                    New SqlParameter("@user_register", Entity.E_PubShare.str_id)                                }        cmdText = "pro_AddUser"        Return helper.ExecuteDataTable(cmdText, CommandType.Text, pt)    End Function

SqlHelper class:

    Public Function ExecuteDataTable(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal pt As SqlParameter()) As Boolean        Using con As New SqlConnection(strCon)            Dim cmd As SqlCommand = con.CreateCommand()                        cmd.CommandText = cmdText                        cmd.CommandType = CommandType.StoredProcedure            cmd.Parameters.AddRange(pt)                      Try                con.Open()                Return cmd.ExecuteNonQuery()                cmd.Parameters.Clear()                           Catch ex As Exception                Return Nothing                Throw ex            End Try        End Using    End Function

3. Review Summary

The stored procedure is not difficult, but I still encountered many problems in the learning process. In the final analysis, I am not very familiar with the SQL language. However, after several times of shelling, we finally learned about it. This is also the significance of restructuring the data center. We have applied the previously learned knowledge to practice, it is a process of checking for missing information and improving knowledge networks. It is inevitable to encounter a problem. Don't worry too much about it. Instead, you should take it as an opportunity to improve yourself. Every small difficulty is overcome and a great leap will be achieved.