SQL syntax advanced application three: stored procedures

I. Overview of stored Procedures

stored procedures in SQL Server are code snippets written using T_sql. It is designed to easily query information from system tables, or to complete administrative tasks and other system administration tasks related to updating database tables. The T_SQL statement is a programming interface between a SQL Server database and an application. In many cases, some code will be repeated by the developer repeatedly, if you write code of the same function each time, not only cumbersome, but also error-prone, and because SQL Server execution of the statement will reduce the operational efficiency of the system.

In short, the stored procedure is SQL Server in order to achieve a specific task, and some need to call the fixed operation statements, or some of the business is too cumbersome, you need to write a lot of logic and query, seriously affect program performance, written into a program segment, these pieces of program are stored on the server, A database server is called through a program.

Second, the advantages of stored procedures

1. Stored procedures speed up the system, and stored procedures are compiled only at creation time, and do not need to be recompiled each time they are executed.
2. Stored procedures can encapsulate complex database operations, simplifying operational processes, such as updating, deleting, and so on to multiple tables.
3. Modular programming can be implemented, stored procedures can be called multiple times, provide a unified database access interface, improve the maintainability of the application.
4. Stored procedures can increase the security of your code, and SQL Server can set user execution permissions on the specified stored procedure for users who cannot directly manipulate the objects referenced in the stored procedure.
5. Stored procedures can reduce network traffic, stored procedure code is stored directly in the database, in the client-server communication process, will not generate a lot of t_sql code traffic.

III. Classification of stored procedures

1. System Stored Procedures

?? A system stored procedure is a stored procedure provided by the SQL Server system itself that can perform various operations as a command.

?? System stored procedures are mainly used to obtain information from the system tables, using system stored procedures to complete the management of the database server, to provide assistance to the system administrator, for users to view the database objects to provide convenient, system stored procedures located in the database server, and start with sp_, System stored procedures are defined in system-defined and user-defined databases and do not have to be called with a database-qualified name before a stored procedure. For example, the sp_rename system stored procedure can modify the name of a user-created object in the current database, sp_helptext stored procedures can display text information for a rule, default value, or view, and many of the administrative work in a SQL Server server is done by executing system stored procedures. Many system information can also be obtained by executing system stored procedures.

?? System stored procedures are created and stored in the system database master, and some system stored procedures can only be used by system administrators, while some system stored procedures may be used by other users through authorization.

2. User stored procedures (custom stored procedures)

?? A custom stored procedure is a collection of T_sql statements written by the user in the user database using the T_SQL statement to implement a particular business requirement, and the custom stored procedure can accept input parameters, return results and information to the client, return output parameters, and so on. When you create a custom stored procedure, a stored procedure name preceded by "# #" indicates that a global temporary stored procedure was created, and the stored procedure preceded by "#" represents the local temporary stored procedure that was created. A local temporary stored procedure can only be used in the reply that created it, and will be deleted when the session ends. Both of these stored procedures are stored in the tempdb database.

User-defined stored procedures fall into two categories: T_sql and CLR

t_sql: A stored procedure is a collection of t_sql statements that hold values, can accept and return user-supplied parameters, and the stored procedure may return data from the database to the client application.

CLR stored procedures refer to method stored procedures that reference the Microsoft.NET Framework common language and can accept and return user-supplied parameters that are implemented as public static methods of the class in. NET framework assemblies.

3. Extended Stored Procedures

? An extended stored procedure is implemented in a dynamic connection (DLL file) that executes outside of a SQL Server environment and can be loaded into an address space that is run by an instance of SQL Server, and extended stored procedures can be programmed with the SQL Server extended stored procedure API, extending the stored procedure prefix "XP _ "to identify, for the user, extended stored procedures and Mandarin stored procedures can be executed in the same way.

Iv. actual reference of the stored procedure

1. Create a non-parametric stored procedure

--1. Creating a non-parametric stored procedure if (exists (SELECT * from sys.objects where name = ' getallbooks '))    drop proc proc_get_studentgocreate proc Edure Getallbooksasselect * from books;--called, executes the stored procedure exec getallbooks;

2. Modifying stored Procedures

Alter procedure Dbo.getallbooks Asselect book_auth from books;

3. Delete a stored procedure

drop procedure Getallbooks;

4. Renaming stored Procedures

Sp_rename Getallbooks,proc_get_allbooks;

5. Creating stored procedures with parameters, which is the most common form of stored procedures

The parameters of a stored procedure are divided into two types: input parameters and output parameters

input parameters : Used to pass in values to stored procedures, similar to those in the Java language or C.

output parameters: used to call the stored procedure, the results of the Conference, similar to the Java language by reference pass.

Value passing and reference passing differences:

• The base data type assignment is a value pass, and the assignment between reference types is reference passing.
• Value passing is a real variable value, and reference passing is the object's reference address.
• After the value is passed, two variables change their respective values; After a reference is passed, two references change the state of the same object

5.1) with one parameter stored procedure

if (exists (SELECT * from sys.objects where name = ' searchbooks '))    drop proc searchbooksgocreate proc Searchbooks (@boo KID int)    AS--requires the BOOK_ID column to be equal to the input parameter    select * from books WHERE [email protected];--execute searchbooksexec searchbooks 1;

5.2) with 2 parameter stored procedures

if (exists (SELECT * from sys.objects where name = ' searchBooks1 '))    drop proc searchbooks1gocreate proc SEARCHBOOKS1 ( c1/> @bookID int,    @bookAuth varchar)    AS--requires book_id and Book_auth columns to be equal to input parameters    select * from books where [email protected] and [email protected];exec searchBooks1 1, ' Jin Yong ';

5.3) create a stored procedure with a return value

if (exists (SELECT * from sys.objects where name = ' Getbookid '))    drop proc getbookidgocreate proc Getbookid (    @book Auth varchar,--input parameter, no default value    @bookId int output--input/output parameter no default) as    select @bookId =book_id from books where [ Email protected]--Execute getbookid The stored procedure with the return value declare @id INT--Declares a variable to receive the return value after executing the stored procedure exec getbookid ' Confucius ', @id outputselect @ ID as Bookid;--as is a name for the returned column value

5.4) Create a stored procedure with wildcard characters

if (exists (SELECT * from sys.objects where name = ' charbooks '))    drop proc charbooksgocreate proc Charbooks (    @book Auth varchar (20) = ' Gold% ',    @bookName varchar = '% ') as     select * from books where Book_auth like @bookAuth and Book_n Ame like @bookName;--Execute stored procedure charbooksexec  charbooks    ' hole% ', '% ';

5.5) Encrypting stored Procedures

The WITH ENCRYPTION clause hides the text of the stored procedure from the user. The following example creates the encryption process, uses the sp_helptext system stored procedure to obtain information about the encryption process, and then attempts to get information about the process directly from the syscomments table.

if (object_id (' books_encryption ', ' P ') is not null)    drop proc books_encryptiongocreate proc books_encryption with ENC Ryptionas     SELECT * FROM books;--perform this procedure books_encryptionexec books_encryption;exec sp_helptext ' books_encryption ';- -the console will display "Object ' books_encryption ' text is encrypted. "

5.6) do not cache stored procedures

--with  recompile does not cache if (object_id (' book_temp ', ' P ') is not null)    drop proc book_tempgocreate proc Book_tempwith Recompileas    SELECT * from books;goexec book_temp;exec sp_helptext ' book_temp ';

5.7) Creating a stored procedure with a cursor parameter

if (object_id (' book_cursor ', ' P ') is not null)    drop proc book_cursorgocreate proc Book_cursor    @bookCursor cursor Varying Outputas    set @bookCursor =cursor forward_only static for    select Book_id,book_name,book_auth from books< C4/>open @bookCursor; go--call book_cursor stored procedure declare @cur cursor,        @bookID int,        @bookName varchar,        @ Bookauth varchar; exec book_cursor @[email protected] Output;fetch next from @cur into @bookID, @bookName, @bookAuth; while (@ @FETCH_STATUS =0) begin to     FETCH next from the @cur into @bookID, @bookName, @bookAuth;    print ' BookID: ' +convert (varchar, @bookID) + ', BookName: ' + @bookName            + ', Bookauth: ' [email protected];endclose @cur    --close cursor deallocate @cur;--Release cursor

5.8) Create a paging stored procedure

if (object_id (' book_page ', ' P ') is not null) drop proc book_pagegocreate proc book_page (@TableName varchar (50), --Table name @ReFieldsStr varchar (200) = ' * ',--field name (all fields are *) @OrderString varchar (200),--sort field (must!) support multiple fields without adding O Rder by) @WhereString varchar ($) =n ",--conditional statement (no add where) @PageSize int,--How many records per page @PageInde x int = 1,--Specifies the current page @TotalRecord int output--Returns the total number of records) Asbegin--processing start and end points Declare @St    Artrecord int;     Declare @EndRecord int;     Declare @TotalCountSql nvarchar (500);        Declare @SqlString nvarchar (2000); Set @StartRecord = (@PageIndex-1) * @PageSize + 1 Set @EndRecord = @StartRecord + @PageSize-1 Set @TotalCountSql = N ' Select @TotalRecord = count (*) from ' + @TableName;--Total number of records statement SET @SqlString = N ' (select Row_number () over (order by ' + @OrderString + ') as RowId, ' [email protected]+ ' from ' + @TableName;--query statement--IF (@WhereString! = ' or @WhereStr      Ing!=null)  BEGIN SET @[email protected] + ' where ' + @WhereString;                    SET @SqlString [email protected]+ ' where ' + @WhereString; END--First execution gets--if (@TotalRecord is null)--BEGIN EXEC sp_executesql @totalCountSql, N ' @TotalRecord in T out ', @TotalRecord output;--returns the total number of records-END----execution subject sentence Set @SqlString = ' select * from ' + @SqlString + ') as T whe    Re rowId between ' + LTrim (str (@StartRecord)) + ' and ' + LTrim (str (@EndRecord)); Exec (@SqlString) end--calls a paged stored procedure book_pageexec book_page ' books ', ' * ', ' book_id ', ', 3,1,0;--declare @totalCount intexec Book_page ' books ', ' * ', ' book_id ', ', 3,1, @totalCount output; The total number of records for select @totalCount as totalcount;--.

PS: Welcome to scan the QR code below or click on the link, join QQ group

SQL syntax advanced application three: stored procedures