Shulou(Shulou.com)05/31 Report--

This article mainly introduces the advantages of sql stored procedures, the article is very detailed, has a certain reference value, interested friends must read it!

Detailed explanation of sql stored procedure example

Stored procedure (Stored Procedure) is a set of SQL statements for specific functions, similar to a programming language, including data types, process control, input and output, and its own function library.

Stored procedure can be said to be a recordset, it is a code block composed of some T-SQL statements, these T-SQL statements code like a method to achieve some functions (single-table or multi-table changes and queries), and then give the code block a name, call him when the function is used. However, the SQL stored procedure is quite abstract and difficult for some beginners to understand, so this article will analyze the SQL stored procedure from shallow to deep to help you learn it.

Advantages of stored procedures

1. Stored procedures are compiled only when they are created, and do not need to be recompiled every time the stored procedure is executed, while generally SQL statements are compiled every time they are executed, so using stored procedures can improve the execution speed of the database and is more efficient than T-SQL statements.

two。 When performing complex operations on a database, the complex operation can be encapsulated in stored procedures in conjunction with the transaction processing provided by the database.

3. A stored procedure can replace a large number of T-SQL statements when the program interacts in the network, so it can also reduce the network traffic and increase the communication rate.

4. Stored procedures can be reused to reduce the workload of database developers.

5. High security and can be set that only some users have the right to use the specified stored procedure

Basic syntax of stored procedure

-create a stored procedure-CREATE PROC [EDURE] procedure_name [ Number] [{@ parameter data_type} [VARYING] [= default] [OUTPUT]] [,... n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS sql_statement [... n]-call stored procedure-EXECUTE Procedure_name''--stored procedure if there are parameters Add the parameter format: @ parameter name = value, or delete the stored procedure directly for the parameter value value--drop procedure procedure_name-you can call another stored procedure in the stored procedure, but not delete another stored procedure

Create parameters for a stored procedure

● procedure_name: the name of the stored procedure, preceded by # for the local temporary stored procedure and # # for the global temporary stored procedure.

● number: an optional integer used to group procedures of the same name so that procedures of the same group can be removed with a single DROP PROCEDURE statement. For example, the procedures used by an application named orders can be named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement removes the entire group. If the name contains a delimiter, the number should not be included in the identifier, only the appropriate delimiter should be used before and after the procedure_name.

● @ parameter: the parameter of the stored procedure. There can be one or more. The user must provide a value for each declared parameter when executing the procedure (unless a default value for that parameter is defined). A stored procedure can have up to 2100 parameters.

● uses the @ symbol as the first character to specify the parameter name. The parameter name must conform to the rules for identifiers. The parameters of each procedure are used only for the process itself; the same parameter name can be used in other procedures. By default, parameters can only replace constants, not table names, column names, or other database object names. For more information, see EXECUTE.

● data_type: the data type of the parameter. All data types, including text, ntext, and image, can be used as parameters to stored procedures. However, the cursor data type can only be used with the OUTPUT parameter. If the specified data type is cursor, you must also specify both the VARYING and OUTPUT keywords. For more information about the data types provided by SQL Server and their syntax, see data types.

Indicates that there is no maximum number of output parameters that can be cursor data types.

● VARYING: specifies the result set that is supported as an output parameter (dynamically constructed by the stored procedure and the content can be changed). Applies only to cursor parameters.

● default: the default value of the parameter. If you define a default value, you do not have to specify a value for that parameter to execute the procedure. The default value must be constant or NULL. If the procedure will use the LIKE keyword for this parameter, the default value can include wildcards (%, _, [], and [^]).

● OUTPUT: indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter using the OUTPUT keyword can be a cursor placeholder.

● RECOMPILE: indicates that SQL Server will not cache the plan for the process, which will be recompiled at run time. Use the RECOMPILE option when using atypical or temporary values and do not want to overwrite an execution plan that is cached in memory.

● ENCRYPTION: represents an entry in the SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the process from being published as part of SQL Server replication. Indicates that during the upgrade process, SQL Server uses the encryption comments stored in syscomments to recreate the encryption process.

● FOR REPLICATION: specifies that stored procedures created for replication cannot be executed at the subscriber. . Stored procedures created with the FOR REPLICATION option can be used as stored procedure filtering and can only be executed during replication. This option cannot be used with the WITH RECOMPILE option.

● AS: specifies the action to be performed by the procedure.

● sql_statement: any number and type of Transact-SQL statements to be included in the procedure. But there are some restrictions.

Case operation learning

Let's take a look at the stored procedure in detail through the table Student. Because you want to understand the simple use of the stored procedure, the example is simple.

No-parameter stored procedure

Select all the information in the Student table

Create proc StuProcas / / here as can not omit begin / / begin and end is a pair, you can not write only one of them, but you can not write either of them, but you can not write either of them. Begin / / begin and end can not be written either.

Stored procedure with parameters

Global variable

Global variables, also known as external variables, are defined outside the function, and their scope is from the definition of the variable to the end of the program file.

Select the student information with the specified name:

Create proc StuProc@sname varchar (100,100) as beginselect ScriptScriptSnamePhone SageDifferent Ssex from student where sname=@snameendgoexec StuProc 'Zhao Lei' / / execute statement

Above, you can assign values to variables on the outside, or you can set default values directly on the inside.

Create proc StuProc@sname varchar = 'Zhao Lei' as beginselect Scripture from student where sname=@snameendgoexec StuProc

You can also output the contents of the variable, using output

Create proc StuProc@sname varchar (100), @ IsRight int output / / outgoing parameters as if exists (select swords, SageJournal, Ssex from student where sname=@sname) set @ IsRight= 1elseset @ IsRight=0godeclare @ IsRight int exec StuProc 'Zhao Lei', @ IsRight outputselect @ IsRight

These are global variables. Let's learn about local variables.

Local variable

Local variables are also called internal variables. Local variables are defined within the function. Its scope is limited to the interior of the function, and it is illegal to use such a variable after leaving the function.

Definition of local variables

You must use the Declare command before you can use it, declare {@ variable name data type}

The method of assigning local variables

Set {@ variable name = expression} or select {@ variable name = expression}

Display of local variables

Create proc StuProcas declare @ sname varchar, set @ sname=', Zhao Lei 'select Scripture, SageJournal, Ssex from student where sname=@snamegoexec StuProc

What if you want to display the data of local variables?

Create proc StuProcas declare @ sname varchar set @ sname= (select Sname from student where slots 01) select @ snamegoexec StuProc

More detailed example operation learning

For example, create a stored procedure PROC_InsertEmployee with a CREATE PROCEDURE statement in the SQL Server query editor window to add information to the employee information table (tb_Employee) and generate automatic numbering at the same time. Its SQL statement is as follows:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Proc_InsertEmployee' AND type =' P') DROP PROCEDURE Proc_InsertEmployee GO CREATE PROCEDURE Proc_InsertEmployee @ PName nvarchar (50), @ PSex nvarchar (4), @ PWage money AS begin declare @ PID nvarchar (50) select @ PID=Max (employee ID) from tb_Employee if (@ PID is null) set @ PID='P1001' else set @ PID='P'+cast (cast (substring (@ PID)) 2PID,@PName,@PSex,@PAge,@PWage 4) as int) + 1 as nvarchar (50) begin insert into tb_Employee values (@ PID,@PName,@PSex,@PAge,@PWage) end end go

Modification of stored procedure

After creating the stored procedure, if you need to modify the functions and parameters of the stored procedure, you can modify it in SQL Server 2005 in the following two ways: one is to modify the stored procedure with Microsoft SQL Server Mangement, and the other is to modify the stored procedure with T-SQL statement.

To modify the stored procedure using Microsoft SQL Server Mangement, follow these steps:

(1) in the object Explorer of SQL Server Management Studio, select the database where you want to modify the stored procedure (such as db_18), and then under that database, select programmability.

(2) Open the "stored procedures" folder, right-click the stored procedure to be modified (such as: PROC_SEINFO), select "modify" command from the pop-up shortcut menu, and the query Editor window will appear. Users can edit the T-SQL code in this window, and after editing, click the "execute (X)" button in the toolbar to modify the code. Users can see the execution result information in the Message window below the query editor.

Use Transact-SQL to modify stored procedures:

Using the ALTER PROCEDURE statement to modify a stored procedure does not affect the permission settings of the stored procedure, nor does it change the name of the stored procedure.

Syntax:

ALTER PROC [EDURE] procedure_name [; number] [{@ parameter data_type} [VARYING] [= default] [OUTPUT]] [,... n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS sql_statement [. N]

Parameter description

Procedure_name: is the name of the stored procedure to change.

Cross-link: the other parameters for the ALTER PROCEDURE statement are the same as the CREATE PROCEDURE statement, see "creating parameters for stored procedures" above.

For example, modify the stored procedure PROC_SEINFO to query employee information over the age of 35. The SQL statement is as follows:

ALTER PROCEDURE [dbo]. [PROC_SEINFO] AS BEGIN SELECT * FROM tb_Employee where employee age > 35 END

Deletion of stored procedure

To delete a stored procedure using Microsoft SQL Server Mangement, follow these steps:

(1) in object Explorer in SQL Server Management Studio, select the database where you want to delete the stored procedure (such as db_student), and then select programmability under that database.

(2) Open the "stored procedures" folder, right-click the stored procedure you want to delete (such as: PROC_SEINFO), and select "Delete" from the pop-up shortcut menu.

(3) Click the OK button to delete the selected stored procedure.

Note: after the datasheet is deleted, the associated stored procedure is not deleted, but its stored procedure cannot be executed.

Use T-SQL to delete stored procedures:

The DROP PROCEDURE statement is used to delete one or more stored procedures or procedure groups from the current database.

Syntax:

DROP PROCEDURE {procedure} [,... n]

Parameter description:

Procedure: is the name of the stored procedure or group of stored procedures to delete. The procedure name must conform to the rules for identifiers. You can choose whether to specify the process owner name, but you cannot specify the server name and database name.

N: is a placeholder that indicates that multiple procedures can be specified.

For example, the SQL statement to delete a PROC_SEINFO stored procedure is as follows.

DROP PROCEDURE PROC_SEINFO

For example, delete multiple stored procedures proc10, proc20, and proc30.

DROP PROCEDURE proc10, proc20, proc30

For example, delete the stored procedure group procs (which contains stored procedures proc1, proc2, proc3).

DROP PROCEDURE procs

Note:

The SQL statement DROP cannot delete a single stored procedure in a stored procedure group.

Apply a stored procedure to verify the user's login identity:

At present, there are many ways to verify the login identity of users, and calling stored procedures to achieve user authentication is one of the best solutions. Because the stored procedure is compiled on the server when it is created, it is much faster to execute than a single SQL statement.

This example verifies that the user name and password of the user login are correct by calling the stored procedure. To run this example, enter the corresponding user name and password in the username and password text boxes, and click the Login button.

Program development steps:

(1) create a new website and name it "index". The default home page is Default.aspx.

(2) the controls involved in the Default.aspx page are shown in Table 1.

(3) the main program code is as follows.

Open SQL Server Management Studio and connect to the database in SQL Server2005. Click the "" button in the toolbar to create a new query editor.

In the query Editor, create a stored procedure PROC_EXISTS that verifies the identity of the logged-in user, with the following SQL statement:

CREATE PROC PROC_EXISTS (@ UserName NVARCHAR (20), @ PassWord NVARCHAR (20), @ ReturnValue int OUTPUT) AS IF EXISTS (select * from tb_member where userName=@UserName AND passWord=@PassWord) set @ ReturnValue= 100 ELSE set @ ReturnValue=-100 GO

Under the Click event of the Login button, execute the stored procedure to verify the login user's identity, and if the user name and password entered are correct, a pop-up dialog box prompts the user to log in successfully, as follows:

Protected void btnLogin_Click (object sender, EventArgs e) {/ / Connect to the database myConn = new SqlConnection (ConfigurationManager.AppSettings ["ConnectionString"] .ToString ()); myCmd = new SqlCommand ("PROC_EXISTS", myConn); / / call the stored procedure to determine whether the user has myCmd.CommandType = CommandType.StoredProcedure / / assign SqlParameter userName=new SqlParameter ("@ UserName", SqlDbType.NVarChar, 20) to the parameters of the stored procedure; userName.Value=this.txtName.Text.Trim (); myCmd.Parameters.Add (userName); SqlParameter passWord=new SqlParameter ("@ PassWord", SqlDbType.NVarChar, 20); passWord.Value = this.txtPassword.Text.Trim (); myCmd.Parameters.Add (passWord) / / indicates that this parameter is the OUTPUT parameter of the stored procedure SqlParameter ReturnValue = new SqlParameter ("@ ReturnValue", SqlDbType.Int, 4); ReturnValue.Direction = ParameterDirection.Output; myCmd.Parameters.Add (ReturnValue); try {myConn.Open (); myCmd.ExecuteNonQuery () If (int.Parse (ReturnValue.Value.ToString ()) = 100) {Response.Write ("alert ('you are a legitimate user, login succeeded!')) ; return;} else {Response.Write ("alert ('the user name and password you entered is incorrect, please re-enter!')) ; return;}} catch (Exception ex) {Response.Write (ex.Message.ToString ());} finally {myConn.Close (); myConn.Dispose (); myCmd.Dispose () }} these are all the contents of the article "what are the advantages of sql stored procedures?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.