Basic knowledge of stored procedures in SQL Server

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

Then why use stored procedures?
1. Stored procedures are only compiled when they are created. There is no need to recompile each time the stored procedure is executed in the future. Generally, SQL statements are compiled once every time they are executed, so using stored procedures can improve the execution speed of the database.
2. When performing complex operations on the database, the complex operations can be encapsulated in stored procedures and used in conjunction with the transaction processing provided by the database.
3. Stored procedures can be reused, which can reduce the workload of database developers.
4. High security, you can set that only certain users have the right to use the specified stored procedure

How to use the stored procedure?
The following uses the Student table to learn about stored procedures. Because we want to understand the simple usage of stored procedures, all examples are very simple.

Stored procedure without parameters:
Select all the information in the Student table,

create proc StuProc
as      //此处 as 不可以省略不写
begin   //begin 和 end 是一对，不可以只写其中一个，但可以都不写
select S#,Sname,Sage,Ssex from student
end
go

Stored procedures with parameters:
Global variables
Global variables are also called external variables, which are defined outside the function. The scope starts from the place where the variable is defined and ends at the end of the program file.
Select the student information with the specified name:

create proc StuProc
@sname varchar(100)   
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go

exec StuProc '赵雷'   //执行语句

The above is to assign a value to the variable externally, or you can directly set the default value for the variable internally

create proc StuProc
@sname varchar(100)='赵雷'
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go

exec StuProc

You can also output the content of the variable, using output

create proc StuProc
@sname varchar(100),
@IsRight int  output //传出参数
as 
if exists (select S#,Sname,Sage,Ssex from student where sname=@sname)
set @IsRight =1
else
set @IsRight=0
go

declare @IsRight int 
exec StuProc '赵雷' , @IsRight output
select @IsRight

The above is a global variable , let’s learn about local variables
Local variables are also called internal variables. Local variables are defined within the function. Its scope is limited to the inside of the function, and it is illegal to use such variables after leaving the function.
The definition of local variables: they must be set with the Declare command before they can be used, declare{@variable name data type}
The assignment method of local variables: set{@variable name=expression} or select{@variable name=expression }
Display of local variables: select @variable name

create proc StuProc
as 
declare @sname varchar(100)
set @sname='赵雷'
select S#,Sname,Sage,Ssex from student where sname=@sname
go

exec StuProc

What if you want to display the data of local variables?

create proc StuProc
as 
declare @sname varchar(100)
set @sname=(select Sname from student where S#=01)
select @sname
go

exec StuProc