SQL Server Stored Procedures

in the reconstruction of the room, there are many places to use the stored procedures, here a small summary.

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures can reduce network traffic and improve performance and data integrity by moving programs that process data from client applications to servers.

1. Create

There are two main ways of doing this:

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

① Click the "+" sign before the database, click "Programmability", find "stored procedure", right click "New Stored Procedure", the system will automatically generate a template, only need to modify the appropriate method.

② Creating stored procedures using T-SQL

CREATE PROCEDURE  Procedure name @Param  parameter type .... @Param  parameter type  output ... Asbegin command line or command block end

For example, create a stored procedure that adds a user:

CREATE procedure [dbo]. [Pro_adduser] @user_id varchar (one), @user_name varchar (one), @user_level varchar, @user_pwd varchar (one), @user_ Register varchar (asinsert) t_user values (@user_id, @user_name, @user_level, @user_pwd, @user_register)

Here are a few things to keep in mind:

* Process Name: Try to start with pro_ or PROC_, it is not recommended to use the SP_ prefix, because the SQL Server system stored procedures are sp_ beginning, so that when the lookup will find the system itself, reduce the query speed.

*begin ... END.. The statements between are primarily the tasks that the stored procedure performs:

Query statement: SELECT

Insert statement: Insert

Updated Statement: Update

Remove statement: Delete

If the stored procedure returns an output parameter or 0, use output

2. Call

(1) calling stored procedures with T-SQL              

EXECUTE procedure Name [parameter value, ...]

(2) On the client code side

D-Tier 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 Boo Lean        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. Reflection and summary

Stored procedures are not difficult, but I still have a lot of problems in the process of learning, in the final analysis is not proficient in SQL language. But after a few jams finally is stumbling to understand the almost, this is our reconstruction of the meaning of the room, the knowledge of the previous learning to really apply to practice, is a leak to improve the knowledge network process. Encounter problems is inevitable, do not bother, and to take it as an opportunity to improve their own, every small difficulty to overcome, and eventually achieve a big leap.