Basic knowledge about SQL stored procedure)

I didn't talk about the stored procedure in the university. After working for a period of time, I still didn't use the stored procedure. I don't need to write the stored procedure at all. Maybe it's the reason for developing the software. For future development, we decided to learn from scratch.

Let's take a look at the definition of the stored procedure,

Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions. The set is compiled and Stored in the database. You can specify the name of the Stored Procedure and provide parameters, if the stored procedure has parameters for execution.

In SQL Server versions, stored procedures are classified into two types: stored procedures provided by the system and custom stored procedures.

The system SP is mainly used to store the master database and prefixed with sp _. The system stored procedure mainly obtains information from the system table, so as to manage the SQL Server for the system administrator.

Common system stored procedures include:

Copy codeThe Code is as follows:
Exec sp_databases; -- view the database
Exec sp_tables; -- view the table
Exec sp_columns student; -- View Columns
Exec sp_helpIndex student; -- view the index
Exec sp_helpConstraint student; -- Constraint
Exec sp_stored_procedures;
Exec sp_helptext 'SP _ stored_procedures '; -- view the stored procedure creation and definition statements.
Exec sp_rename student, stuInfo; -- modify the names of tables, indexes, and columns.
Exec sp_renamedb myTempDB, myDB; -- change the Database Name
Exec sp_defaultdb 'master', 'mydb'; -- change the default database name
Exec sp_helpdb; -- Database Help, query database information
Exec sp_helpdb master;

A user-defined stored procedure is created by a user and can complete a specific function, for example, a stored procedure for querying user required data information.

Here we will look at the advantages of stored procedures;

(1) reuse. Stored procedures can be reused to reduce the workload of database developers.

(2) improve performance. The stored procedure is compiled when it is created. You do not need to re-compile the stored procedure in the future. A general SQL statement needs to be compiled every time it is executed, so the efficiency is improved by using the stored procedure.

(3) reduce network traffic. The stored procedure is stored on the server. You only need to pass the name and parameters of the stored procedure when calling the procedure. This reduces the amount of data transmitted over the network.

(4) Security. Parameterized stored procedures can prevent SQL injection attacks and apply Grant, Deny, and Revoke permissions to stored procedures.

 

Let's take a look at the basic syntax created.
Copy codeThe Code is as follows:
Syntax for defining stored procedures

Create proc [EDURE] stored procedure name

@ Parameter 1 data type = default value,

...... ,

@ Parameter n data type OUTPUT

AS

SQL statement

GO
The parameter is optional.
Parameters are divided into input parameters and output parameters.
The input parameter can have default values.

Create a simple Stored Procedure
 

Create procedure UserLogin
@ Name varchar (20 ),
@ Password varchar (20)

AS

-- Defines a variable temporarily used to save the password
-- DECLARE @ strPwd NVARCHAR (20). variables are not described here. This article will be detailed later.
BEGIN
Select * from userinfo where userName = @ name and userPass = @ password
END
GO

First, we use a simple SQL query

Select * from userinfo where userName = 'admin'

Query results:

---------------------

UserName UserPass

Admin

Now let's execute our Stored Procedure
Exec UserLogin admin, admin

-- Or call it like this:
EXEC UserLogin @ name = 'admin', @ password = 'admin'

Query results:

---------------------

UserName UserPass

Admin

Note: in SQL SERVER, all User-Defined variables start with "@". The OUTPUT keyword indicates that this parameter is used for OUTPUT, and AS is the content of the stored procedure. If you run the preceding code once in the "query analyzer", SQL SERVER creates a stored procedure named "UserLogin" in the current database. You can open "Enterprise Manager", select the database for the current operation, and select "programmable-> stored procedure" in the tree list on the left ", now you can see the stored procedure you just created in the list on the right (if not, refresh it ).

We can see two methods for calling stored procedures in data (EXEC is equivalent to EXECUTE );

EXEC process name parameter value 1, parameter value 2 ,....

Or

EXEC parameter 1 = parameter value 1, parameter 2 = parameter value 2 ....

We can see above.

To delete a stored procedure, use drop

Like this

Copy codeThe Code is as follows:
Drop PROCEDURE UserLogin

What is the purpose of creating such a stored procedure? It is not just to view the competition in the data.

We are working on a web or winform program. Suppose we need a login. Well, we can call this stored procedure to log on. Based on the input parameters, if there is a query record, this record exists in the Database, indicating that the logon is successful. Otherwise, the logon fails.

This method is safer to prevent SQL injection.