SQL Server-based stored procedures

Read Catalogue

• A: Overview of stored procedures
• II: Classification of stored procedures
• Three: Create a stored procedure
• 1. Create a non-parametric stored procedure
• 2. Modifying stored Procedures
• 3. Delete a stored procedure
• 4. Renaming stored Procedures
• 5. Creating a stored procedure with parameters

?? In a nutshell, a stored procedure is a collection of one or more SQL statements, which can be treated as a batch file, but its role is not limited to batch processing. This article mainly introduces the use of variables, the creation, invocation, viewing, modification, and deletion of stored procedures and stored functions.

Back to top one: 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 implement a specific task, and some need to call a number of fixed action statements are written into a program segment, which are stored on the server, the database server through the program to invoke.

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.

Disadvantages of stored procedures:

1. Database porting is inconvenient, the stored procedure relies on the database management system, the operation code encapsulated in SQL Server stored procedure cannot be migrated directly into other database management system.
2. Object-oriented design is not supported, the logical business cannot be encapsulated in an object-oriented manner, or even a common business logic framework for supporting services can be formed.
3. Code is poorly readable and difficult to maintain. Cluster is not supported.

Back to Top II: Stored Procedure classification

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.

Back to top three: creating a stored procedure

工欲善其事, its prerequisite, prepare the data as follows:

Use sample_db;--creating Test Books Table CREATE table books (    book_id int identity () primary key,    book_name varchar (20),    book_price float,    book_auth varchar (10));--inserting test data insert into books (Book_name,book_price,book_auth)                    Values                        (' Analects ', 25.6, ' Confucius '),                        (' Tianlong eight ', 25.6, ' Jin Yong '),                        (' Snow Mountain flying fox ', 32.7, ' Jin Yong '),                        (' Ordinary World ', 35.8, ' Lu Yao '),                        (' Shiji ', 54.8, ' Sima Qian ');

　　

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 a stored procedure with parameters

? The parameters of the 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

(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;

　　(2) with 2 parameter stored procedures

if (exists (SELECT * from sys.objects where name = ' searchBooks1 '))    drop proc searchbooks1gocreate proc SEARCHBOOKS1 ( c8/> @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 ';

　　(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]--executes 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

　　(4) Creating 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) 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. "

　　(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 ';

　　(7). Create 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

　　(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;--.

　　

SQL Server-based stored procedures