SQL Server Basics: Stored Procedures

Simply put, a stored procedure is a collection of one or more SQL statements, which can be regarded as a batch file, but its role is not limited to batch processing. This article mainly introduces the use of variables, the creation, calling, viewing, modifying and deleting operations of stored procedures and stored functions.

1: Overview of stored procedures

The stored procedures in SQL Server are code segments written using T_SQL. Its purpose is to easily query information from system tables, or to complete management tasks related to updating database tables and other system management tasks. The T_SQL statement is the programming interface between the SQL Server database and the application program. In many cases, some codes will be written repeatedly by developers. If the code with the same function is written every time, it will not only be cumbersome and error-prone, but also reduce the operating efficiency of the system because SQL Server executes statements one by one.

In short, a stored procedure means that in order to achieve a specific task, SQL Server writes some fixed operation statements that need to be called multiple times into program segments. These program segments are stored on the server, and the database server calls them through the program.

Advantages of stored procedures:

Stored procedures speed up system operation. Stored procedures are only compiled when they are created and do not need to be recompiled every time they are executed in the future.

Stored procedures can encapsulate complex database operations and simplify the operation process, such as updating, deleting multiple tables, etc.

It can realize modular programming, and the stored procedure can be called multiple times, providing a unified database access interface and improving the maintainability of the application.

Stored procedures can increase the security of the code. For users who cannot directly operate the objects referenced in the stored procedures, SQL Server can set the user's execution permissions for the specified stored procedures.

Stored procedures can reduce network traffic. The stored procedure code is stored directly in the database. During the communication process between the client and the server, a large amount of T_SQL code traffic will not be generated.

Disadvantages of stored procedures:

Database transplantation is inconvenient. Stored procedures depend on the database management system. The operation code encapsulated in SQL Server stored procedures cannot be directly transplanted to other database management systems.

Does not support object-oriented design, cannot encapsulate logical business in an object-oriented way, or even form a general business logic framework that can support services.

The code is poorly readable and difficult to maintain. Clustering is not supported.

2: Classification of stored procedures

1. System stored procedures

System stored procedures are stored procedures provided by the SQL Server system itself, which can be used as commands to perform various operations.

System stored procedures are mainly used to obtain information from system tables. Use system stored procedures to complete the management of the database server, provide help to system administrators, and provide convenience for users to view database objects. The system stored procedures are located in the database server, and Starting with sp_, system stored procedures are defined in system-defined and user-defined databases, and there is no need to add a database qualified name before the stored procedure when calling. For example: the sp_rename system stored procedure can modify the name of a user-created object in the current database, the sp_helptext stored procedure can display text information of rules, default values ​​or views, and many management tasks in the SQL SERVER server are completed by executing system stored procedures. , much system information can also be obtained by executing system stored procedures.

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

2. User stored procedures (custom stored procedures)

Custom stored procedures are a collection of T_SQL statements written by users using T_SQL statements in order to achieve a specific business requirement in the user database. Custom stored procedures can Accept input parameters, return results and information to the client, return output parameters, etc. When creating a custom stored procedure, adding "##" in front of the stored procedure name indicates that a global temporary stored procedure is created; adding "#" in front of the stored procedure indicates that a local temporary stored procedure is created. A local temporary stored procedure can only be used within the session in which it was created and will be deleted when the session ends. Both stored procedures are stored in the tempdb database.

User-defined stored procedures are divided into two categories: T_SQL and CLR

T_SQL: Stored procedures are a collection of value-saved T_SQL statements that can accept and return user-provided parameters. Stored procedures may also be returned from the database to the client application data.

CLR stored procedures refer to method stored procedures that use the Microsoft.NET Framework common language. They can accept and return parameters provided by the user. They are implemented as public static methods of classes in the .NET Framework assembly.

3. Extended stored procedures

Extended stored procedures are implemented as dynamic connections (DLL files) executed outside the SQL SERVER environment. They can be loaded into the address space where the SQL SERVER instance is running and executed. Extended stored procedures can be executed using SQL SERVER extended stored procedure API programming, extended stored procedures are identified by the prefix "xp_". For users, extended stored procedures are the same as Mandarin stored procedures and can be executed in the same method.

Three: Create a stored procedure

If you want to do your job well, you must first sharpen your tools. Prepare the data as follows:

use sample_db;
--创建测试books表
create table books (
 book_id int identity(1,1) primary key,
 book_name varchar(20),
 book_price float,
 book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
 values
 ('论语',25.6,'孔子'),
 ('天龙八部',25.6,'金庸'),
 ('雪山飞狐',32.7,'金庸'),
 ('平凡的世界',35.8,'路遥'),
 ('史记',54.8,'司马迁');

Create a stored procedure without parameters

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
 drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

Modify the stored procedure

alter procedure dbo.getAllBooks 
as
select book_auth from books;

Delete the stored procedure

drop procedure getAllBooks;

Rename the stored procedure

sp_rename getAllBooks,proc_get_allBooks;

Create a stored procedure with parameters Stored procedures
The parameters of stored procedures are divided into two types: input parameters and output parameters

Input parameters: used to pass values ​​into the stored procedure, similar to the value transfer in Java language or C.

Output parameters: used for meeting participation results after calling the stored procedure, similar to passing by reference in Java language.

The difference between transfer by value and transfer by reference:

Assignment to basic data types is transfer by value; assignment between reference types is transfer by reference.
Passing by value passes the actual variable value; passing by reference passes the reference address of the object.
After the value is passed, the two variables change their respective values; after the reference is passed, the two references change the state of the same object

(1) Stored procedure with one parameter

if (exists (select * from sys.objects where name = 'searchBooks'))
 drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
 --要求book_id列与输入参数相等
 select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(2) With 2 parameters Stored procedure

if (exists (select * from sys.objects where name = 'searchBooks1'))
 drop proc searchBooks1
go
create proc searchBooks1(
 @bookID int,
 @bookAuth varchar(20)
)
as
 --要求book_id和book_Auth列与输入参数相等
 select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(3) Create a stored procedure with a return value

MySQL

if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字
if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(4) Create a stored procedure with wildcards

if (exists (select * from sys.objects where name = 'charBooks'))
 drop proc charBooks
go
create proc charBooks(
 @bookAuth varchar(20)='金%',
 @bookName varchar(20)='%'
)
as 
 select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec charBooks '孔%','论%';

(5) Encrypt the stored procedure

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

if (object_id('books_encryption', 'P') is not null)
 drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
 select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6). Do not cache the stored procedure

--with recompile不缓存
if (object_id('book_temp', 'P') is not null)
 drop proc book_temp
go
create proc book_temp
with recompile
as
 select * from books;
go
exec book_temp;
exec sp_helptext 'book_temp';

(7). Create a stored procedure with cursor parameters

if (object_id('book_cursor', 'P') is not null)
 drop proc book_cursor
go
create proc book_cursor
 @bookCursor cursor varying output
as
 set @bookCursor=cursor forward_only static for
 select book_id,book_name,book_auth from books
 open @bookCursor;
go
--调用book_cursor存储过程
declare @cur cursor,
 @bookID int,
 @bookName varchar(20),
 @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
 fetch next from @cur into @bookID,@bookName,@bookAuth;
 print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
 +' ,bookAuth: '+@bookAuth;
end
close @cur --关闭游标
DEALLOCATE @cur; --释放游标

(8). Create a paging stored procedure

if (object_id('book_page', 'P') is not null)
 drop proc book_page
go
create proc book_page(
 @TableName varchar(50), --表名
 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*)
 @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by)
 @WhereString varchar(500) =N'', --条件语句(不用加where)
 @PageSize int, --每页多少条记录
 @PageIndex int = 1 , --指定当前为第几页
 @TotalRecord int output --返回总记录数
)
as
begin
 --处理开始点和结束点
 Declare @StartRecord 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;--总记录数语句
 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
 --
 IF (@WhereString! = '' or @WhereString!=null)
 BEGIN
 SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString;
 SET @SqlString =@SqlString+ ' where '+ @WhereString; 
 END
 --第一次执行得到
 --IF(@TotalRecord is null)
 -- BEGIN
 EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
 -- END
 ----执行主语句
 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
 Exec(@SqlString) 
END
--调用分页存储过程book_page
exec book_page 'books','*','book_id','',3,1,0;
--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;--总记录数。