SQL stored Procedures

1. Understanding Stored Procedures

After learning the beginner's SQL Operation plan to go deep down the index, but the learning process is a lot of practical operations are stored procedures, so you can only master the stored procedures to further into the index. There are 3 advantages to using stored procedures: The stored procedure is a compiled code block, so the execution efficiency is higher than T-SQL, the use of stored procedures can not write a large number of T-SQL statements, improve the communication rate, through the stored procedure can enable users without permission to access the database indirectly, to ensure the security of data. In SQL stored procedures are divided into system stored procedures and user-defined stored procedures, the system stored procedures are mainly prefixed with sp_, the following is a common system stored procedures, in my SQL test success.

execsp_databases;--View DatabaseexecSp_tables;--View TableexecSp_helpindex Myindex;--View IndexexecSp_stored_procedures;--View ConstraintsexecSp_helptext'Sp_stored_procedures';--to view the specific code for a system stored procedureexecSp_rename mytable3,mytable5;--Modify the name of a table, index, columnexecSp_renamedb Test,test1;--Modify the name of the databaseexecsp_helpdb test1;--Querying database Information

Of course, the focus of this article is the summary of my school's custom stored procedures, next is a very interesting place. Here's the basic syntax for creating stored procedures, and I don't like to look at the basic syntax when I first started, but using the sp_helptext above to see the code of the system stored procedure you know the basic structure is important, and then the complex stored procedure is the basic structure. The first line is to create a stored procedure and give the stored procedure a name (procedure_name). The second line is a group ID, and we can set multiple stored procedures to the same set so that you can delete them in bulk. The third line is declaring the variable, the variable can have one or more, after the @ plus the variable name, the name needs to conform to the naming convention. Output description This parameter is the return parameter, which can be returned to Exec[ute]. Varying is a result set that is specified as an output parameter and applies only to the cursor parameter, which is dynamically constructed by the stored procedure and the contents can be changed. Row Five is an additional option that recompile indicates that SQL does not cache the schedule for the procedure, which is recompiled at run time, and is used when you use atypical or temporary values and do not want to overwrite the execution plan that is cached in memory. Encryption can encrypt entries in the syscomments table that contain the text of the CREATE PROCEDURE statement. For replication specifies that the stored procedure created by replication cannot be performed at the Subscriber, the stored procedure that you create with the For REPLICATION option can be used as a stored procedure filter and can only be executed during the copy process, and it cannot be used with the WITH recompile. As is followed by the T-SQL statement to execute.

--create a stored procedure where [] means optional, {} indicates requiredCREATE PROC [edure]procedure_name[; number]    [{@parameter data_type}[VARYING] [= Default] [OUTPUT]]    [,... N]    [With {RECOMPILE | Encryption | RECOMPILE, encryption}]    [For REPLICATION] asSql_statement[. .. n]

2. Basic use of stored procedures

I created a new student table, and here is the field for this table and the code for the stored procedure I wrote.

--Query the data table and add a new piece of dataCreate procMyProc asSelect *  fromstudent;Insert  intoStudentValues('20131003','Harry', -,'male', the);GoexecMyProc;Drop procMyProc;--stored procedures with input parameters, output parameters, and return values, and result setsCreate procMyProc@pragender nvarchar( -),@stuCount intOutput asif(@pragender='male')Select @stuCount=SUM(Stunum) fromStudentwhereGender='male';ElseSet @stuCount=0;Select *  fromstudent;return @stuCount;GoexecMyProc'male',NULL;Drop procMyProc;--Modify stored procedures, output Stucount variables, note that another stored procedure cannot be deleted in one stored procedure, only another stored procedure can be calledAlter procMyProc@stuCount intOutput asSet @stuCount=9;Select @stuCount;GoexecMyProcNULL;Drop procMyProc;--After writing the example above, you can see that it ends with go. Go plays a role in the stored procedure, and the official website explains that go signals the end of a batch of SQL Server-T statements. --The simple point of understanding is that go identifies a set of SQL statements and two SQL statements between go as a batch statement. Go is not a T-SQL and cannot be on the same line as T-SQL. There's also a begin and end that you can interpret as curly braces in C #--There is also a print in SQL that does not belong to a function, it can print out a string or a Unicode string constant and any valid character data type variable. --However, the data type of this variable must be either char or varchar or a data type that can be converted to char or varchar with privacy. In addition, 3 benefits were found in the search for information.--Box drawings char (9)--NewLine char (TEN)--enter char (in)Create procMyProc asPrint 'AAA'+Char(9)+'AAA';Print 'AAA'+Char(Ten)+'AAA';Print 'AAA'+Char( -)+'AAA';execMyProc;--Paging Stored Proceduresif(object_id('MyProc','P') is  not NULL)    Drop procMyProcGoCreate procMyProc (@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  byStunumASC) as  Number  fromstudent) TwhereT. Number between @startRow  and @endRow;execMyProc1,2;

For the paging stored procedure I was a little dizzy at first, but I got to see it in a quiet way. For select *,row_number () over (order by stunum ASC) as number from student; this statement, We can directly perform a look at what effect, such as. You can see row_number This function adds a new column to the data table found and assigns the column to a small to large value. Paging is based on this number for paging.

3. Stored procedures in conjunction with transactions

When an error occurs in a stored procedure, you can use rollback to guarantee the atomicity of the data, and here is a very simple transaction procedure that I wrote.

Create procMyProc (@stuNum int) as    SetNocount on;--set NOCOUNT to ON, which means no more statistics are returned.    Declare @myerror int; begin Transaction         UpdateStudentSetStuname='Zhang San' whereStunum=1; Select @myerror=@ @ERROR;--with 2 @ is a global variable        if @myerror !=0            begin                GotoError_HandlerEnd    Commit Transaction    SetNocountoff    return 0    --handle error, transaction rollback If an error occursError_Handler:rollback Transaction    SetNocountoff    return @myerror    Go

SQL stored Procedures