About the basics of getting started with SQL stored procedures

There is nothing mentioned about stored procedures in college. After working for a while, I still have no use for stored procedures. There is no need to write stored procedures at all. This may be due to the direction of the software. For future development, I decided to learn from scratch.

Here is the definition of stored procedure.

Stored Procedure (Stored Procedure) is a set of SQL statements to complete specific functions. It is compiled and stored in the database. The user specifies the name of the stored procedure and gives the parameters. , if the stored procedure is executed with parameters.

In the series versions of SQL Server, stored procedures are divided into two categories: system-provided stored procedures and user-defined stored procedures.

　 System SP is mainly stored in the master database, and is prefixed with sp_. The system stored procedure mainly obtains information from system tables, thereby managing SQL Server for system administrators.

Commonly used system stored procedures are:

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

User-defined stored procedures are created by users and can complete a specific function, such as: stored procedures that query the data information required by the user.

Here we look at the benefits of stored procedures;

(1) Reuse. Stored procedures can be reused, reducing the workload of database developers.

(2) Improve performance. Stored procedures are compiled when they are created and do not need to be recompiled when used in the future. General SQL statements need to be compiled every time they are executed, so using stored procedures improves efficiency.

(3) Reduce network traffic. The stored procedure is located on the server. When calling, you only need to pass the name and parameters of the stored procedure, thus reducing the amount of data transmitted over the network.

(4) Safety. Parameterized stored procedures can prevent SQL injection attacks, and Grant, Deny, and Revoke permissions can be applied to stored procedures.

Okay, let’s take a look at the basic syntax of creation

定义存储过程的语法
    CREATE  PROC[EDURE]  存储过程名
              @参数1  数据类型 = 默认值,
               …… ,
              @参数n  数据类型 OUTPUT
            AS
            SQL语句
    GO
,参数是可选的
,参数分为输入参数、输出参数
,输入参数允许有默认值
  这里来创建一个简单的存储过程
 
CREATE PROCEDURE UserLogin
@name varchar(20),
@password varchar(20)
AS
-- 定义一个临时用来保存密码的变量
--DECLARE @strPwd NVARCHAR(20) 这里先不介绍变量。稍后的文章会详细讲到
BEGIN
select * from userinfo where userName=@name and userPass=@password
END
GO
首先我们用简单的sql查询
select * from userinfo where userName='admin'
查询结果：
---------------------
UserName  UserPass
Admin      Admin
现在我们来执行我们的存储过程
exec UserLogin admin,admin
--或这样调用：
EXEC UserLogin @name='admin',@password='admin'
查询结果：
---------------------
UserName  UserPass
Admin      Admin

Note that in SQL SERVER, all user-defined variables start with "@", and the OUTPUT keyword indicates that this parameter is used for output, AS After that comes the content of the stored procedure. As long as the above code is executed once in "Query Analyzer", SQL SERVER will create a stored procedure named "UserLogin" in the current database. You can open "Enterprise Manager", select the database you are currently operating on, and then select "Programmability -> Stored Procedures" in the tree list on the left. At this time, you can see the database you just created in the list on the right. The stored procedure is there (if not, just refresh it).

I saw that there are two ways to call a stored procedure in the data (EXEC and EXECUTE are equivalent here);

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

or

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

We also saw it above.

If you want to delete a stored procedure, use drop

like this

drop PROCEDURE UserLogin

What’s the use of creating such a stored procedure? It’s not just about viewing the data.

We are making a web or winform program. Suppose we need a login. Well, we can call this stored procedure to log in. According to the parameters passed in, if there is a record in the query, then this record exists in the database, indicating login. Success, otherwise failure.

This is more secure and can prevent sql injection.