Basic knowledge of SQL Server stored procedures

What is a stored procedure?
A stored procedure is one or more SQL commands that are stored as executable objects in the database.
In layman's terms: a stored procedure is actually a set of SQL statements that can perform certain operations.

So why use stored procedures?
1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can increase the speed of the database execution.
2. When complex operations are performed on a database, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database.
3. Stored procedures can be reused to reduce the workload of database developers.
4. High security, can be set only some users have the right to use the specified stored procedures

How does the stored procedure work?

The following table Feili is used to understand the stored procedure, because it is to understand the simple use of stored procedures, so all the examples are simple.

No parameter stored procedure:
Select all the information in the Feili table,

ALTER PROCEDURE [dbo]. [Feili]
As
BEGIN
--Routine body goes here, e.g.
--SELECT ' Navicat for SQL Server '
SELECT * from Feilipoint
END

There are parameter stored procedures:
Global variables
A global variable, also known as an external variable, is defined outside the function and is scoped to the end of the program file, starting at the definition of the variable.
Select the fertility information for the specified ID:

ALTER PROCEDURE [dbo]. [Feili]
(@OBJECTID nvarchar (100))

DECLARE @sql1 nvarchar (MAX);
BEGIN
SET @sql1 = ' SELECT * from Feilipoint where objectid= ' [email protected]
EXEC sp_executesql @sql1;
END

The above is an external assignment to the variable, or you can set the default value directly to the variable internally

ALTER PROCEDURE [dbo]. [Feili] (@OBJECTID nvarchar (100) = ' 1 ') As Beginselect * from Feilipoint where [email protected]exec feili; END

You can also export the contents of the variable, using the output

　

ALTER PROCEDURE [dbo]. [Feili] (@OBJECTID nvarchar (100)) As DECLARE @sql1 nvarchar (MAX);D eclare @num INT; Beginset @sql1 = ' SELECT @num =count (*) from Feilipoint where objectid= ' [email protected]exec sp_executesql @sql1, N ' @num int output ', @num output; SELECT @num; END

The above is the global variable, below to understand the local variable
Local variables are also known as internal variables. A local variable is defined within a function as a description. The scope is limited to the inside of the function, and it is illegal to use the variable after leaving the function.
Definition of a local variable: You must use the DECLARE command before it can be used, declare{@ variable name data type}
Assignment method for local variables: set{@ variable name = expression} or select{@ variable name = expression}
Display of local variables: SELECT @ variable Name

ALTER PROCEDURE [dbo]. [Feili] As DECLARE @objectid nvarchar (100); begindeclare @sql1 nvarchar (MAX); SET @objectid = ' 10 '; SET @sql1 = ' SELECT * from Feilipoint WHERE objectid= ' [email protected]; EXEC sp_executesql @sql1; END

Can also be written like this

ALTER PROCEDURE [dbo]. [Feili] As DECLARE @objectid nvarchar (100);//If the variable needs to be used dynamically, it needs to be declared in exec begindeclare @sql1 nvarchar (MAX); SET @objectid = ' 10 '; SET @sql1 = ' SELECT * from feilipoint WHERE [email protected] '; EXEC sp_executesql @sql1, N ' @objectid nvarchar (max) ', @objectid; END

　　

Basic knowledge of SQL Server stored Procedures (RPM)