SQL Server Stored Procedures

A stored procedure in Transact-SQL, very similar to a method in the Java language, can be called repeatedly. After the stored procedure executes once, the statement can be cached so that the statements in the cache are used directly at the next execution. This can improve the performance of your stored procedures.

Ø The concept of stored procedures

Stored procedure procedure is a set of SQL statements that are compiled to complete a particular function, stored in a database, and executed by specifying the name of the stored procedure and giving parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, the stored procedure runs faster than a single SQL statement block. At the same time, because it only needs to provide the stored procedure name and the necessary parameter information in the call, it can reduce the network traffic and the simple network burden to some extent.

1, the advantages of stored procedures

A, stored procedure allows standard component-type programming

After a stored procedure is created, it can be invoked more than once in a program, without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, but have no effect on the application source code, which greatly improves the portability of the program.

B, the stored procedure can achieve faster execution speed

If an operation contains a large number of T-SQL statement code that is executed more than once, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives the storage plan in the system tables that are eventually present when a stored procedure is first run. A batch of T-SQL statements needs to be precompiled and optimized each time it is run, so it will be slower.

C, stored procedures to reduce network traffic

For the same operation on a database object, if the T-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client, the call statement is passed in the network, otherwise it will be multiple SQL statements. This reduces network traffic and lowers Network load.

D, stored procedures can be used as a security mechanism to make full use of

The system administrator can restrict the execution of a stored procedure, which can restrict some data access, avoid unauthorized users ' access to the data, and ensure the security of the data.

Ø System Stored Procedure

System stored procedures are system-created stored procedures designed to facilitate querying information from system tables or to complete administrative tasks or other system administration tasks related to updating database tables. System stored procedures are primarily stored in the master database and stored procedures that begin with an "SP" underscore. Although these system stored procedures are in the master database, we can still invoke system stored procedures in other databases. There are some system stored procedures that are automatically created in the current database when a new database is created.

Common system stored procedures are:

sp_databases;        --View database sp_tables; --View table sp_columns student;--View column sp_helpindex student;--view index sp_helpconstraint student;--constraint sp_stored_procedures; sp_helptext;--View stored procedure creation, definition statement sp_rename student, stuinfo;--Modify table, index, column name Sp_renamedb mytempdb, mydb;--change database name sp_ Defaultdb,;--Change the default database for logins sp_helpdb;--database help, query database information sp_helpdb master;

System Stored Procedure Example:

--table renaming sp_rename,; * stud;--rename named sp_rename,,; sp_help;--Rename index sp_rename n, n, N; sp_help;--Query all stored procedures * sys.objects type =; * sys.objects Type_desc name;

Ø user-defined stored procedures

1. Create a grammar

|    Pro_name [{@ parameter data type} [= default] [], {@ parameter data type} [= default] [], ....] Sql_statements

2. Create a stored procedure with no parameters

--Create a stored procedure ((* sys.objects name =)) proc_get_student proc_get_student * student;--call, execute stored procedure proc_get_student ;

3. Modifying stored Procedures

--Modify the stored procedure proc_get_student * student;

4. Stored procedure with parameter

--With parameter stored procedure (OBJECT_ID (,)) Proc_find_stu Proc_find_stu (@startId, @endId) * Student ID @startId @endId Pro C_find_stu 2, 4;

5. Parameter stored procedure with wildcard characters

--with wildcard parameter stored procedure (OBJECT_ID (,)) Proc_findstudentbyname Proc_findstudentbyname (@name () =, @nextName (20) =) * Student name @name name @nextName; Proc_findstudentbyname; Proc_findstudentbyname,;

6. Stored procedure with output parameters

(object_id (,)) Proc_getstudentrecord Proc_getstudentrecord (@id,--Default input parameter @name (20),--Output parameter @age (20 )--Input and output parameters @name = name, @age = Age Student id = @id sex = @age; @id, @name (a), @temp (20);  @id = 7; @temp = 1; Proc_getstudentrecord @id, @name, @temp; @name, @temp; @name + + @temp;

7. Do not cache stored procedures

--RECOMPILE does not cache (OBJECT_ID (,)) proc_temp proc_temp RECOMPILE * student; Proc_temp;

8. Encrypt stored procedures

--Encrypt with encryption (OBJECT_ID (,)) proc_temp_encryption proc_temp_encryption encryption * student; Proc_temp_encryption; sp_helptext; sp_helptext;

9. Stored procedure with cursor parameters

(object_id ()) proc_cursor proc_cursor @cur @cur = forward_only ID, name, age student; @cur;--Call @exec_cur; @id, @name (a), @age; Proc_cursor @cur = @exec_cur;--Call stored procedure @exec_cur @id, @name, @age;      (@ @fetch_status = 0) @exec_cur @id, @name, @age; + (, @id) + + @name + + (, @age); @exec_cur; @exec_cur;--delete a cursor

   10, paging stored procedures

---stored procedures, row_number complete paging   (object_id (, )    )       proc_ cursor  pro_page     @startIndex  ,     @endIndex         (*)   product;         *    (         row_number ()   (  pid)    rowId, *  product     )  temp      temp.rowid   @startIndex    @endIndex--  pro_page pro_page 1, 4   (object_id (, )    )       pro_stu  pro_stu (      @pageIndex  ,     @pageSize  )        @startRow  ,  @endRow        @startRow  =  (@pageIndex   - 1)  *  @pageSize &NBsp;+1      @endRow  =  @startRow  +  @pageSize  -1      *   (         *, row_number ()    (  id )   number  student     )  t      t.number   @startRow    @endRow; pro_stu 2, 2;

Øraiserror

RAISERROR returns a user-defined error message, you can specify a severity level, and set the system variable to record the error that occurred.

The syntax is as follows:

({msg_id | msg_str | @local_variable} {, severity,} [, argument[,... N]] [[,... N]])

# msg_id: User-defined error information specified in the sysmessages system table

# msg_str: User-defined information with a maximum length of 2047 characters.

# severity: User defined The severity level associated with the message. When you use msg_id to raise a user-defined message created with sp_addmessage, the severity specified on RAISERROR overrides the severity defined in sp_addmessage.

Any user can specify a severity level of 0-18 directly. Only users who are commonly used by the sysadmin fixed server role or have ALTER trace permissions can specify a 19-25 direct severity level. The security level between 19-25 requires the WITH LOG option.

# state: Any integer from 1 to 127 directly. The state default value is 1.

(, 16, 1); * sys.messages;--uses messages defined in sysmessages (33003, 1);(33006, 16, 1);

SQL Server Stored Procedures