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 Procedures
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:
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;--constraint exec sp_stored_p Rocedures; exec sp_helptext ' sp_stored_procedures ';--view stored procedure creation, define statement exec sp_rename student, stuinfo;--Modify table, index, column name exec sp_renamedb mytempdb, mydb;--change the database name exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for logins exec sp_helpdb;--database help, query database information exec sp_helpdb Master
System Stored Procedure Example:
--Table rename exec sp_rename ' stu ', ' stud '; SELECT * FROM stud; --Rename named exec sp_rename ' stud.name ', ' sName ', ' column '; exec sp_help ' stud '; --Rename index exec sp_rename n ' student.idx_cid ', n ' Idx_cidd ', 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 the name like ' sp% ';
? User-defined stored procedures
1. Create a grammar
create proc | Procedure Pro_name [{@ parameter data type} [= default] [output], {@ parameter data type} [= default] [output], ... ] as sql_ Statements
2. Create a stored procedure with no parameters
--Create a stored procedure if (exists (SELECT * from sys.objects where name = ' proc_get_student ')) drop proc proc_get_student go create Proc Proc_get_student as select * from student;--invoke, execute stored procedure exec proc_get_student;
3. Modifying stored Procedures
--Modify the stored procedure alter proc proc_get_student AS SELECT * from student;
4. Stored procedure with parameter
--with parameter stored procedure if (object_id (' Proc_find_stu ', ' P ') is not null) drop proc proc_find_stu go create proc proc_find_stu (@startI d int, @endId int) as select * from student where ID between @startId and @endId go EXEC proc_find_stu 2, 4;
5. Parameter stored procedure with wildcard characters
---with wildcard parameter stored procedure if (object_id (' proc_findstudentbyname ', ' P ') is not null) drop proc proc_findstudentbyname go create Pro C Proc_findstudentbyname (@name varchar = '%j% ', @nextName varchar = '% ') as select * from student where name L Ike @name and name like @nextName; GO EXEC 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_getstudentrecord go create proc Proc_getstude Ntrecord ( @id int,--default input parameter @name varchar () out,--output parameter @age varchar () output--input/output parameter) as SELECT @ Name = name, @age = age from student where id = @id and sex = @age; go-- declare @id int, @name varchar (20), c8/> @temp varchar (20); Set @id = 7; Set @temp = 1; 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_temp go create proc proc_temp with R Ecompile as select * from student; go EXEC proc_temp;
8. Encrypt stored procedures
--Encrypt with encryption if (object_id (' proc_temp_encryption ', ' P ') are NOT null) drop proc Proc_temp_encryption go CREATE proc proc_temp_encryption with encryption as select * from student; go EXEC proc_temp_encryption; exec sp_helpt Ext ' 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_cursor go create proc proc_cursor @cur cursor Varyin G Output as 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, @age int, exec proc_cursor @cur = @exec_cur output;--call stored procedure fetch NEXT from @e Xec_cur into @id, @name, @age; while (@ @fetch_status = 0) The BEGIN fetch next from the @exec_cur into @id, @name, @age; print ' ID: ' + convert (varchar, @id) + ', Name: ' + @name + ', Age: ' + convert (char, @age); End Close @exec_cur; Deallocate @exec_cur;--delete cursor
10. Paging Stored Procedure
---the stored procedure, Row_number completes the paging if (object_id (' pro_page ', ' P ') is not null) drop proc proc_cursor go create proc pro_page @ StartIndex int, @endIndex int as select COUNT (*) from product; SELECT * FROM ( select Row_number () over (order by PID) as RowId, * from product ) temp where Temp.rowid betwee n @startIndex and @endIndex go--drop proc pro_page exec pro_page 1, 4----Paging stored procedure if (object_id (' pro_page ', ' P ') is not n ull) drop proc pro_stu go CREATE PROCEDURE pro_stu ( @pageIndex int, @pageSize int) as declare @startRo w int, @endRow int Set @startRow = (@pageIndex-1) * @pageSize +1 Set @endRow = @startRow + @pageSize-1 s Elect * FROM ( select *, Row_number () + (ORDER BY ID ASC) as number from student ) T where T.number BETW Een @startRow and @endRow; EXEC 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:
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 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.
RAISERROR (' is error ', 16, 1); SELECT * from Sys.messages; --Use the message defined in sysmessages RAISERROR (33003, 16, 1); RAISERROR (33006, 16, 1);
====================================----------------------------------------=================================== ===
database transaction processing in SQL Server is an important concept, but also a little bit difficult to understand, many SQL beginners write transaction processing code is often a vulnerability, this article describes three different methods, examples of how to write the correct code in the stored procedure transaction.
When writing SQL Server transaction-related stored procedure Code, you often see the following notation:
BEGIN TRAN UPDATE statement 1 ... UPDATE Statement 2 ... Delete Statement 3 ... Commit Tran
There is a great danger in writing SQL. Take a look at the following example:
CREATE TABLE demo (ID int not null) go to begin tran INSERT INTO demo values (NULL) INSERT INTO demo values (2 ) Commit Tran go
There is an error message that violates the NOT NULL constraint when executing, but then prompts (1 row (s) affected). After we executed the SELECT * from demo, we found that insert into demo values (2) was executed successfully. What is the reason for this? It turns out that SQL Server, when a runtime error occurs, rollback the statement that caused the error by default and continues with the subsequent statement.
How to avoid this problem? There are three ways to do this:
1. Precede the transaction statement with SET XACT_ABORT on
Set XACT_ABORT on BEGIN TRAN UPDATE statement 1 ... UPDATE Statement 2 ... Delete Statement 3 ... Commit Tran Go
When the Xact_abort option is on, SQL Server terminates execution and rollback the entire transaction when an error is encountered.
2. Once each individual DML statement executes, the execution status is immediately judged and handled accordingly.
BEGIN TRAN UPDATE statement 1 ... If @ @error <> 0 begin Rollback tran goto labend End DELETE statement 2 ... If @ @error <> 0 begin Rollback tran goto labend End Commit tran labend: Go
3. In SQL Server 2005, you can take advantage of the Try...catch exception handling mechanism
BEGIN Tran begin try UPDATE statement 1 ... Delete Statement 2 ... End Try begin catch if @ @trancount > 0 rollback tran end catch if @ @trancount > 0 Commit t Ran go
The following is a simple stored procedure that demonstrates the transaction process.
CREATE PROCEDURE Dbo.pr_tran_inproc as begin SET NOCOUNT on BEGIN TRAN UPDATE statement 1 ... if @ @error <> 0 begin Rollback TRAN return-1 end DELETE statement 2 ... If @ @error <> 0 begin Rollback TRAN return-1 end Commit Tran return 0 end Go