Summary of SQL server stored procedures

Time:2020-5-28

Introduction to stored procedures:

Stored procedure is a set of SQL statements in a large database to complete specific functions. It is stored in the database and is permanently valid after a compilation. The user executes the stored procedure by specifying its name and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database. In the case of huge amount of data, the efficiency can be increased by using stored procedures

——From Baidu Encyclopedia

Types of stored procedures:

1 system stored procedure

With SP_ At the beginning, it is used for setting, obtaining information and relevant management of the system.

2 local stored procedure

A stored procedure created by a user is a stored procedure created by a user to complete a specific function. In fact, a stored procedure generally refers to a local stored procedure.

3 temporary stored procedure

There are two types of stored procedures:

The first is local temporary stored procedure. If the first character of its name is the hash (ා), the stored procedure will become a local temporary stored procedure stored in tempdb database, and only the user who created it can execute it;

The second is the global temporary stored procedure, which starts with two hash marks (ා), then the stored procedure will become a global temporary stored procedure stored in the tempdb database. Once the global temporary stored procedure is created, any user connected to the server can execute it, and does not need specific permissions.

4 remote stored procedures

In SQL Server 2005, remote stored procedures are stored procedures located on a remote server. Usually, a remote stored procedure can be executed using distributed queries and execute commands.

5 extended stored procedures

Extended stored procedures are stored procedures that users can write in an external programming language, and the name of extended stored procedures is usually XP_ start.

——From Baidu Encyclopedia

Advantages of stored procedures:

① Reuse. Stored procedures can be reused to reduce the workload of database developers.

② Reduce network traffic. The stored procedure is located on the server. When calling, only the name and parameters of the stored procedure need to be passed, so the amount of data transmitted by the network is reduced.

③ Security. Parameterized stored procedures can prevent SQL injection attacks, and grant, deny, and revoke permissions can be applied to stored procedures.

Briefly:

1. Stored procedures are compiled only when they are created, and they do not need to be recompiled every time they are executed later. In general, SQL statements are compiled every time they are executed, so using stored procedures can improve the speed of database execution.

2. When performing complex operations on the database (such as updating, inserting, querying, deleting multiple tables), the complex operations stored procedures can be encapsulated and used together with the transaction processing provided by the database.

3. Stored procedures can be reused to reduce the workload of database developers

4. high security. It can be set that only some users have the right to use the specified stored procedure

It should be noted that the so-called “stored procedures execute faster than SQL statements” widely spread on the Internet is actually a misunderstanding and groundless. People inside Microsoft don’t agree with this, so it can’t be regarded as a formal advantage. I hope you can realize this.

Disadvantages of stored procedures:

1: Debugging is troublesome.

2: Database migration is not convenient, stored procedure depends on database management system, the operation code encapsulated in SQL server stored procedure can not be directly transplanted to other database management system.

3: Recompile problem, because the back-end code is compiled before running, if the object with reference relationship changes, the affected stored procedures and packages will need to be recompiled (but it can also be set to compile automatically at run time).

4: If a large number of stored procedures are used in a program system, the data structure will change with the increase of user requirements when the program is delivered and used. Then there are related problems of the system. Finally, if the user wants to maintain the system, it can be said that it is very difficult, and the cost is unprecedented, and maintenance is more difficult.

5: It does not support object-oriented design, can not use object-oriented way to encapsulate logical business, or even form a general business logic framework that can support services.


Stored procedure instance:

Create books table

use Test;
–Create test books table
create table Money (
id int identity(1,1) primary key,
money int
);

Parameterless stored procedure

–Create a parameterless stored procedure
if exists(select * from sysobjects where name=’usp_getAllMoneyInfo’)
drop proc usp_getAllMoneyInfo
go

Create stored procedure

–The stored procedure is equivalent to a function that can have parameters. After the parameter as is output, return is used for the method body to return–
create proc usp_getAllMoneyInfo
–Before as, to define the parameter is equivalent to the parameter in the function, no declaration is required–
as
select * from Money
go

Execute stored procedure

–Execute stored procedure

exec usp_getAllMoneyInfo;

Delete stored procedure

–Delete stored procedure
drop proc usp_getAllMoneyInfo;
go

Modifying stored procedures

alter proc usp_getAllMoneyInfo
as
select Money.money from Money
go

Create a stored procedure with parameters

if exists(select * from sysobjects where name=’usp_getMoneyInfo’)
drop proc usp_getMoneyInfo
go
–Create a stored procedure with parameters and execute
create proc usp_getMoneyInfo
–As before, for defining parameters, parameters in functions need not be declared
@id int
as
select * from Money where [email protected]
go
exec usp_ Getmoneyinfo 2 — add parameters after the call
go

Create a stored procedure with the output parameter

–Create a stored procedure with the output parameter and execute
if exists(select * from sysobjects where name=’usp_getAllMoneyCount’)
drop proc usp_getAllMoneyCount
go
create proc usp_getAllMoneyCount
@Count int output — the external parameter is followed by an output similar to C ා
as
set @count = (select count(*) from Money)
go

declare @cnt int;
exec usp_ getAllMoneyCount @ [email protected] Output — keyword to be added when calling
print @cnt

Create a stored procedure with a return value

–Create a stored procedure with a return value and execute
–Stored procedure with return value–
–Return can only return integer data. If you need to return other types of data, you need to use the output output parameter–
if exists(select * from sysobjects where name=’usp_getCount’)
drop proc usp_getCount
go
create proc usp_getCount
as
declare @count int
set @count = (select count(*) from Money)
return @count
go
declare @totalCount int
exec @totalCount = usp_getCount
print @totalCount

Articles are constantly updated, if you like, please take up your lovely hands and give me some praise!

Praise is a positive attitude towards life. Praise one!