SQL Server Stored Procedure

Stored Procedures in Transact-SQL are very similar to methods in Java. They can be called repeatedly. After the stored procedure is executed once, you can cache the statements so that the statements in the cache are directly used for the next execution. This improves the performance of stored procedures.

Concept of Stored Procedure

Stored Procedure is a set of SQL statements for specific functions. Compiled and stored in the database. You can specify the name of the stored Procedure and provide parameters for execution.

Stored Procedures can contain logical control statements and data manipulation statements. They can accept parameters, output parameters, return one or more result sets, and return values.

Since the stored procedure is compiled on the database server and stored in the database at the time of creation, the stored procedure runs faster than a single SQL statement block. At the same time, because you only need to provide the stored procedure name and necessary parameter information during the call, it can also reduce network traffic and simple network burden to a certain extent.

1. Advantages of Stored Procedures

A. stored procedures allow standard component programming

After a stored procedure is created, it can be called and executed multiple times in the program without re-writing the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time, but it has no impact on the application source code, which greatly improves the program portability.

B. Fast execution of Stored Procedures

If an operation contains a large number of T-SQL statement codes that are executed multiple times, the stored procedure is much faster than the batch execution. Because the stored procedure is pre-compiled, when a stored procedure is run for the first time, the query optimizer analyzes and optimizes the stored procedure and provides the storage plan that is finally stored in the system table. The T-SQL Statement of batch processing needs to be pre-compiled and optimized every time, so the speed will be slower.

C. reduce network traffic through stored procedures

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

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

The system administrator can restrict the permissions of a stored procedure to prevent unauthorized users from accessing data and ensure data security.

System stored procedures

A system stored procedure is a stored procedure created by the system. It is designed to conveniently query information from the system table or complete management tasks related to updating database tables or other system management tasks. The system stored procedures are mainly stored in the master database and stored procedures starting with "sp. Although these system stored procedures are in the master database, we can still call the 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 include:

Exec sp_databases; -- View database exec sp_tables; -- View table exec sp_columns student; -- View column exec sp_helpIndex student; -- View index exec sp_helpConstraint student; -- restrict exec sp_stored_procedures; exec sp_helptext 'SP _ stored_procedures '; -- view the stored procedure creation and Definition Statement exec sp_rename student, stuInfo; -- modify the table, index, and column name exec sp_renamedb myTempDB, myDB; -- change the database name exec sp_defaultdb 'master', 'mydb'; -- change the default database exec sp_helpdb for the login name; -- Database Help, query the database information exec sp_helpdb master;

System Stored Procedure example:

-- Rename the table exec sp_rename 'stu', 'stud'; select * from stud; -- rename the column exec sp_rename' stud. name', 'sname', 'column '; exec sp_help 'stud'; -- rename the index exec sp_rename n' student. idx_cid ', n'idx _ cid', n'index'; exec 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 procedures

1. Create a syntax

Create proc | procedure pro_name [{@ parameter data type} [= default value] [output], {@ parameter data type} [= default value] [output],...] as SQL _statements

2. Create a stored procedure without Parameters

-- Create a stored procedure if (exists (select * from sys. objects where name = 'proc _ get_student ') drop proc proc_get_studentgocreate proc proc_get_studentas select * from student; -- call and execute the 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) as select * from student where id between @ startId and @ endIdgoexec proc_find_stu 2, 4;

5. Stored Procedures with wildcard Parameters

-- If (object_id ('proc _ findstudentbyname', 'P') is not null) drop proc proc_findStudentByNamegocreate proc proc_findStudentByName (@ name varchar (20) = '% j %', @ nextName varchar (20) = '%') as select * from student where name like @ name and name like @ nextName; goexec proc_findStudentByName; exec proc_findStudentByName '% o %','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 and output parameter) as select @ 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 = 1; exec proc_getStudentRecord @ id, @ name out, @ temp output; select @ name, @ temp; print @ name + '#' + @ temp;

7. Non-Cache stored procedures

-- With recompile, if (object_id ('proc _ temp ', 'P') is not null) drop proc proc_tempgocreate proc proc_tempwith recompileas select * from student; goexec proc_temp;

8. Encrypted storage process

-- Encryption with encryption if (object_id ('proc _ temp_encryption ', 'P') is not null) drop proc using proc proc_temp_encryptionwith encryptionas select * from student; goexec proc_temp_encryption; exec sp_helptext 'proc _ temp '; exec sp_helptext 'proc _ temp_encryption ';

9. Stored Procedures with cursor Parameters

If (object_id ('proc _ cursor ', 'P') is not null) drop proc proc_cursorgocreate proc proc_cursor @ cur cursor varying outputas set @ cur = cursor forward_only static for select 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 the Stored Procedure fetch next from @ exec_cur into @ id, @ name, @ age; while (@ fetch_status = 0) begin fetch 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 a cursor

10. Paging Stored Procedure

--- Stored procedure and row_number complete paging if (object_id ('Pro _ page', 'P') is not null) drop proc proc_cursorgocreate proc pro_page @ startIndex int, @ endIndex intas select count (*) from product; select * from (select row_number () over (order by pid) as rowId, * from product) temp where temp. rowId between @ startIndex and @ endIndexgo -- drop proc pro_pageexec pro_page 1, 4 ---- paging stored procedure if (object_id ('Pro _ page', 'P') is not null) drop proc pro_stugocreate procedure pro_stu (@ pageIndex int, @ pageSize int) as declare @ startRow int, @ endRow int set @ startRow = (@ pageIndex-1) * @ pageSize + 1 set @ endRow = @ startRow + @ pageSize-1 select * from (select *, row_number () over (order by id asc) as number from student) t where t. number between @ startRow and @ endRow; exec pro_stu 2, 2;

Ø Raiserror

Raiserror returns the User-Defined error message. You can specify the Severity Level and set the system variable record errors.

Syntax:

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

# Msg_id: User-Defined error information specified in the sysmessages system table

# Msg_str: User-defined information. The maximum length of the information is 2047 characters.

# Severity: The severity level associated with the message. When msg_id is used to trigger a user-defined message created using sp_addmessage, the specified severity on raiserror overwrites the severity defined in sp_addmessage.

Any user can specify 0-18 direct severity levels. Only users frequently used by sysadmin fixed server roles or having the alter trace permission can specify a direct Severity Level. The with log option is required for security levels between and.

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

Raiserror ('is error', 16, 1); select * from sys. messages; -- use the message raiserror (33003, 16, 1) defined in sysmessages; raiserror (33006, 16, 1 );