https://www.cnblogs.com/c-supreme/p/9482321.html
In short, a stored procedure is a collection of one or more sql statements, which can be regarded as a batch file, but its function is not limited to batch processing.
1, Overview of stored procedures
Stored procedures in SQL server use t_ Code snippet written in SQL. Its purpose is to easily query information from system tables, or complete management tasks related to updating database tables and other system management tasks. T_SQL statement is the programming interface between SQL Server database and application. In many cases, some code will be written repeatedly by developers. If you write code with the same function every time, it will not only be cumbersome, but also prone to errors. Moreover, the execution of SQL Server statement by statement will reduce the operation efficiency of the system.
In short, a stored procedure is that SQL Server writes some fixed operation statements that need to be called many times into program segments in order to achieve a specific task. These program segments are stored on the server and called by the database server through the program.
Advantages of stored procedures:
Stored procedures speed up the system. Stored procedures are compiled only when they are created, and there is no need to recompile each time they are executed in the future.
Stored procedures can encapsulate complex database operations and simplify the operation process, such as updating and deleting multiple tables.
Modular programming can be realized, stored procedures can be called many times, a unified database access interface is provided, and the maintainability of the application program is improved.
Stored procedures can increase the security of code. For objects referenced in stored procedures that cannot be directly operated by users, SQL Server can set the execution permission of users for specified stored procedures.
Stored procedure can reduce network traffic. Stored procedure code is directly stored in the database. In the communication process between client and server, a large number of T will not be generated_ SQL code traffic.
Disadvantages of stored procedures:
The database migration is inconvenient. The stored procedure depends on the database management system. The operation code encapsulated in the SQL Server stored procedure cannot be directly transplanted to other database management systems.
It does not support object-oriented design, can not encapsulate the business logic in an object-oriented way, and even form a general business logic framework that can support services
The code has poor readability and is difficult to maintain. Clustering is not supported.
Describe your understanding of stored procedures: stored procedures in SQL server are similar to functions and in C # and can be understood as a function that can carry parameters to stored procedures, return values, output parameters, or default values. In this way, you can quickly understand stored procedures
2, Classification of stored procedures
1. System stored procedure
System stored procedures are stored procedures provided by the SQL Server system itself, which can be used as commands to perform various operations.
The system stored procedure is mainly used to obtain information from the system table, use the system stored procedure to complete the management of the database server, provide help for the system administrator, and provide convenience for users to view database objects. The system stored procedure is located in the database server and is stored in sp_ At the beginning, the system stored procedure is defined in the system defined and user-defined database. When calling, it is not necessary to add the database qualified name before the stored procedure. For example: SP_ The rename system stored procedure can modify the name of the user created object in the current database, sp_ The helptext stored procedure can display the text information of rules, default values or views. Many management tasks in the SQL SERVER server are completed by executing the system stored procedure, and many system information can also be obtained by executing the system stored procedure.
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 procedure (user defined stored procedure)
Custom stored procedure, that is, the user uses T_SQL statement, written in the user database in order to achieve a specific business requirement_ A collection of SQL statements. 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 "##" before the stored procedure name indicates that a global temporary stored procedure has been created; adding "#" before the stored procedure indicates that a local temporary stored procedure has been created. A local temporary stored procedure can only be used in the reply that created it and will be deleted at the end of the session. Both stored procedures are stored in the tempdb database.
User defined stored procedures fall into two categories: T_SQL and CLR
T_SQL: the stored procedure is a T with saved values_ A collection of SQL statements that can accept and return parameters provided by users. Stored procedures may also return data from the database to client applications.
CLR stored procedures refer to method stored procedures that reference the Microsoft.NET Framework common language. They can accept and return user provided parameters. They are implemented as public static methods of classes in. NET Framework assemblies.
3, Creation of stored procedures
--1.Create data table
--Use a database, followed by the database name
use HSFX_ShenZhenDB_TEST;
--Create test books surface
create table Money (
id int identity(1,1) primary key,
money int
);
--2.Insert several pieces of test data into it
--(1).Create a parameterless stored procedure
--If stored procedures exist in the current database system usp_getAllMoneyInfo，Then delete the stored procedure and re create it.
if exists(select * from sysobjects where name='usp_getAllMoneyInfo')
drop proc usp_getAllMoneyInfo
go
--A stored procedure is equivalent to a function. It can have parameters and output parameters as The latter is used by the method body return Go back--
create proc usp_getAllMoneyInfo
--as The parameters defined above are equivalent to the parameters in the function without declaration--
as
select * from Money
go
--Execute the stored procedure (actually execute it) sql sentence select * from Money Query the contents of this table)
exec usp_getAllMoneyInfo
--Delete stored procedure
-- drop proc usp_getAllMoneyInfo
--Modify stored procedure
-- alter procedure usp_getAllMoneyInfo
-- as
-- **********
-- go
--2.Create a stored procedure with parameters and execute it
if exists(select * from sysobjects where name='usp_getMoneyInfo')
drop proc usp_getMoneyInfo
go
--A stored procedure is equivalent to a function. It can have parameters and output parameters as The latter is used by the method body return Go back--
create proc usp_getAllMoneyInfo
--as The parameters defined above are equivalent to the parameters in the function without declaration--
@id int
as
select * from Money where id=@id
go
exec usp_getMoneyInfo 2 --Add parameters after the call--
declare @cnt int;
exec usp_getAllMoneyCount @count=@cnt output --Add when calling output Keywords for-
print @cnt
--4.Create a stored procedure with a return value and execute it
--Stored procedure with return value--
--return You can only return integer data. If you need to return other types of data, you need to use output Output parameters--
if exists(select * from sysobjects where name='usp_getCount')
drop proc usp_getCount
go
create proc usp_getCount
as
declare @count int --declare Declare a int Variables of type are called@count,Assign number to@count
set @count = (select count(*) from Money)
return @count
go
declare @totalCount int
exec @totalCount = usp_getCount --Get the format of the return value: exec Add the return value variable later=--
print @totalCount