SQL Server Stored Procedure

Definition of stored procedures,

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.
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.

Syntax for defining stored procedures

CREATEPROC [EDURE] stored procedure name
@ Parameter 1 data type = default value,
...... ,
@ Parameter n data type OUTPUT
AS
SQL statement
GO
The parameter is optional.
Parameters include input parameters and output parameters.
Default values are allowed for input parameters.
Create a simple Stored Procedure

Create procedure UserLogin @ name varchar (20), @ password varchar (20)
AS
-- Define a variable temporarily used to save the password -- DECLARE @ strPwd NVARCHAR (20. Later, the article will detail 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

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.

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.