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

This article focuses on "the syntax of sql general stored procedures". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the syntax of sql general stored procedures.

Stored procedures in Transact-SQL are very similar to methods in the Java language and can be called repeatedly. When the stored procedure is executed once, the statement can be cached so that the statement in the cache can be used directly the next time it is executed. This can improve the performance of stored procedures.

The concept of Z. stored procedure

A stored procedure Procedure is a set of SQL statements that are compiled and stored in the database in order to complete a specific function. The user executes the stored procedure 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 the stored procedure is compiled on the database server and stored in the database at creation time, the stored procedure runs faster than a single block of SQL statements. At the same time, because only the stored procedure name and necessary parameter information need to be provided during the call, the network traffic and simple network burden can be reduced to a certain extent.

1. Advantages of stored procedures

A. stored procedures allow standard component programming

After the stored procedure is created, it can be called and executed many times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, but it has no effect on the application source code, which greatly improves the portability of the program.

B. stored procedures can achieve faster execution speed.

If an operation contains a large amount of T-SQL statement code, which is executed multiple times, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled, when a stored procedure is run for the first time, the query optimizer analyzes and optimizes it, and gives the storage plan that will eventually be stored in the system table. Batch T-SQL statements need to be precompiled and optimized every time they are run, so they are slower.

C, stored procedures reduce network traffic

For the same operation against 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, only the call statement is passed in the network, otherwise there will be multiple SQL statements. As a result, the network traffic is reduced and the network load is reduced.

D. Stored procedures can be fully utilized as a security mechanism.

The system administrator can restrict the permissions of a stored procedure executed, so as to realize the restrictions on some data access, avoid unauthorized users' access to the data, and ensure the security of the data.

System stored procedure

The system stored procedure is the stored procedure created by the system, which aims to easily query the information from the system table or complete the management tasks related to updating the database table or other system management tasks. System stored procedures are mainly stored in the master database, starting with an underscore "sp". Although these system stored procedures are in the master database, we can still call system stored procedures in other databases. Some system stored procedures are automatically created in the current database when a new database is created.

Common system stored procedures are:

Exec sp_databases;-- View database exec sp_tables;-- View tables exec sp_columns student;-- View columns exec sp_helpIndex student;-- View Index exec sp_helpConstraint student;-- constraints exec sp_stored_procedures;exec sp_helptext 'sp_stored_procedures';-- View stored procedure creation, definition statements exec sp_rename student, stuInfo;-- modify the names of tables, indexes, columns exec sp_renamedb myTempDB, myDB Change the database name exec sp_defaultdb 'master',' myDB';-- change the default database exec sp_helpdb;-- database help for login name, query database information exec sp_helpdb master

Example of system stored procedures:

-- Table rename exec sp_rename 'stu',' stud';select * from stud;-- column rename exec sp_rename 'stud.name',' sName', 'column';exec sp_help' stud';-- rename index exec sp_rename Numbstudent.idxesteridicides, Noble indexicidicides, Noble indexExec sp_help 'student';-- query all stored procedures select * from sys.objects where type =' P' Select * from sys.objects where type_desc like'% pro%' and name like 'sp%'

User-defined stored procedure

1. Create syntax

Create proc | procedure pro_name [{@ parameter data type} [= default] [output], {@ parameter data type} [= default] [output],.... ] asSQL_statements

2. Create a stored procedure without parameters

-- create stored procedure if (exists (select * from sys.objects where name = 'proc_get_student')) drop proc proc_get_studentgocreate proc proc_get_studentasselect * from student;-- to call and execute stored procedure exec proc_get_student

3. Modify the stored procedure

-- modify the stored procedure alter proc proc_get_studentasselect * from student

4. Stored procedure with parameters

-- if (object_id ('proc_find_stu',' P') is not null) drop proc proc_find_stugocreate proc proc_find_stu (@ startId int, @ endId int) asselect * from student where id between @ startId and @ endIdgoexec proc_find_stu 2,4

5. Stored procedures with wildcard parameters

-- stored procedure if (object_id ('proc_findStudentByName',' P') is not null) drop proc proc_findStudentByNamegocreate proc proc_findStudentByName with wildcard parameters (@ name varchar (20) ='% j% from student where name like, @ nextName varchar (20) ='%') asselect * from student where name like @ name and name like @ nextName;goexec proc_findStudentByName;exec proc_findStudentByName'% o% stores,'t%'

6. Stored procedure with output parameters

If (object_id ('proc_getStudentRecord',' P') is not null) drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord (@ id int,-- default input parameter @ name varchar (20) out,-- output parameter @ age varchar (20) output-- input / output parameter) asselect @ name = name, @ age = age from student where id = @ id and sex = @ age Go-- declare @ id int, @ name varchar (20), @ temp varchar (20); set @ id = 7; set @ temp = 1bot exec proc_getStudentRecord @ id, @ name out, @ temp output;select @ name, @ temp;print @ name +'#'+ @ temp

7. Do not cache stored procedures

-- WITH RECOMPILE does not cache if (object_id ('proc_temp',' P') is not null) drop proc proc_tempgocreate proc proc_tempwith recompileasselect * from student;goexec proc_temp

8. Encrypted stored procedure

-encrypting WITH ENCRYPTION if (object_id ('proc_temp_encryption',' P') is not null) drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionasselect * from student;goexec proc_temp_encryption;exec sp_helptext 'proc_temp';exec sp_helptext' proc_temp_encryption'

9. Stored procedure with cursor parameters

If (object_id ('proc_cursor',' P') is not null) drop proc proc_cursorgocreate proc proc_cursor @ cur cursor varying outputasset @ cur = cursor forward_only static forselect id, name, age from student;open @ cur;go-- call declare @ exec_cur cursor;declare @ id int, @ name varchar (20), @ age int;exec proc_cursor @ cur = @ exec_cur output -- call stored procedures fetch next from @ exec_cur into @ id, @ name, @ age;while (@ @ fetch_status = 0) beginfetch next from @ exec_cur into @ id, @ name, @ age;print'id:'+ convert (varchar, @ id) +', name:'+ @ name +', age:'+ convert (char, @ age); endclose @ exec_cur;deallocate @ exec_cur;-- delete cursors

10. Paging stored procedure

-stored procedure, row_number complete paging if (object_id ('pro_page',' P') is not null) drop proc proc_cursorgocreate proc pro_page @ startIndex int, @ endIndex intasselect count (*) from product Select * from (select row_number () over (order by pid) as rowId, * from product) tempwhere temp.rowId between @ startIndex and @ endIndexgo--drop proc pro_pageexec pro_page 1,4 Murray-paging stored procedure if (object_id ('pro_page',' P') is not null) drop proc pro_stugocreate procedure pro_stu (@ pageIndex int, @ pageSize int) asdeclare @ startRow int EndRow intset @ startRow = (@ pageIndex-1) * @ pageSize + 1set @ endRow = @ startRow + @ pageSize-1select * from (select *, row_number () over (order by id asc) as number from student) twhere t.number between @ startRow and @ endRow Exec pro_stu 2, 2

Dead Raiserror

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

The syntax is as follows:

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

# msg_id: user-defined error message specified in the sysmessages system table

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

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

Any user can specify a direct severity level of 0-18. Only users who are commonly used in the sysadmin fixed server role or have alter trace privileges can specify a direct severity level of 19-25. The security level between 19 and 25 requires the with log option.

# state: any integer between 1 and 127direct. The State default value is 1.

Raiserror ('iserror', 16, 1); select * from sys.messages;-- uses the message raiserror defined in sysmessages (33003, 16, 1); raiserror (33006, 16, 1). Now that you have a better understanding of the syntax of sql general stored procedures, you might as well do it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.