Introduction to SQL Server stored procedures Learning _mssql

The definition of a stored procedure,

A stored procedure (Stored Procedure) is a set of SQL statements that is compiled and stored in a database to complete a specific function, and the user specifies the name of the stored procedure and gives the parameter if the stored procedure is executed with parameters.
In the series version of SQL Server, stored procedures fall into two categories: system-supplied stored procedures and user-defined stored procedures. The system SP, which is primarily stored in the master database, is prefixed with sp_ and the system stored procedures primarily obtain information from the system tables to administer SQL Server for the system administrator.
User-defined stored procedures are created by the user and can perform a specific function, such as a stored procedure that queries the data information required by the user.

Here we look at the benefits of the stored procedure;

(1) repeated use. Stored procedures can be reused to reduce the workload of database developers.
(2) Improve performance. Stored procedures are compiled when they are created and are not recompiled for future use. Normal SQL statements need to be compiled once per execution, so using stored procedures improves efficiency.
(3) Reduce network traffic. The stored procedure is on the server, and when invoked, it is only necessary to pass the name of the stored procedure and the parameters, thus reducing the amount of data transmitted by the network.
(4) security. Parameterized stored procedures can prevent SQL injection attacks and can apply grant, deny, and revoke permissions to stored procedures.

Okay, let's take a look at the basic syntax created

Defining the syntax of a stored procedure

Createproc[edure] Stored Procedure name
@ Parameter 1 Data type = default value,
...... ,
@ parameter n data type output
As
SQL statement
Go
parameter is optional
Parameters are divided into input parameters, output parameters
The input parameter allows a default value
Here to create a simple stored procedure

CREATE PROCEDURE userlogin@name varchar (m), @password varchar (20)
As
--Define a variable that is temporarily used to hold the password--declare @strPwd NVARCHAR (20) This is not about variables. Later articles will detail the beginselect * from UserInfo where username= @name and userpass= @passwordENDGO
First we use a simple SQL query
SELECT * from userinfo where username= ' admin '
Query results:
---------------------
UserName Userpass
Admin Admin

Now we're going to execute our stored procedure.
EXEC userlogin Admin,admin
--or This call:
EXEC userlogin @name = ' admin ', @password = ' admin '

Query results:
---------------------
UserName Userpass
Admin Admin

Note that in SQL Server, all user-defined variables begin with "@", and the OUTPUT keyword indicates that the parameter is used for outputting, followed by the stored procedure content. As soon as the above code is executed in Query Analyzer, SQL Server creates a stored procedure named "Userlogin" in the current database. You can open "Enterprise Manager", select the database of the current operation, and then select "Programmable-> stored Procedures" in the tree list on the left, where you can see the stored procedure you just created in the list on the right (if not, refresh it).
There are two ways to call a stored procedure in your data (here 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've seen it above.

What is the use of creating such a stored procedure, instead of just viewing the game in the data?
We are doing a Web or WinForm program, assuming that we need a login, OK, we can invoke this stored procedure to log in, according to the parameters passed in, if the query out of the record, then this record in the database, indicating that the login succeeded, or failure.
This is done in a more secure way to prevent SQL injection.