SQL Server Stored Procedures

in the reconstruction of the room, there are a lot of 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 complete a specific function. The user runs it by specifying the name of the stored procedure and giving the number of parameters (assuming that the stored procedure has a number of parameters). Stored procedures can reduce network traffic by moving programs that process data from the client application to the server. and improve performance and data integrity.

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, and then click Programmability. Find the "stored procedure" and right-click on "New Stored procedure", the system will generate a template on its own initiative. You just need to change the corresponding method.

② Creating stored procedures using T-SQL

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

Like what. Create a stored procedure to join the 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 some points to note:

* Process Name: Try to start with Pro_ or PROC_. Using sp_ as a prefix is not recommended. Because the SQL Server system's stored procedures start with sp_, the lookup will look for the system itself and reduce the query speed.

*begin ... END.. The statement between the main is the task that the stored procedure will run:

Query statement: SELECT

Insert statement: Insert

Updated Statement: Update

Remove statement: Delete

Assuming that the stored procedure returns an output parameter or 0, use output

2. Call

(1) calling stored procedures with T-SQL              

EXECUTE procedure name [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 root of the SQL language is not skilled enough. Just after a few jams finally stumbled on the understanding of almost the same, which is what we re-structure the meaning of the room. The knowledge that has been learned is really applied to practice, it is a process of leak-checking and good knowledge net. Encounter problems is inevitable, not too much trouble, and to take it as an opportunity to improve their own, every small difficulty to overcome, and finally achieve a big leap.

SQL Server Stored Procedures