SQLServer 存储过程

存储过程（Stored Procedure） 是一种预编译的SQL代码块，它被存储在数据库中，并可以通过名称调用执行。存储过程可以包含一个或多个SQL语句，用于完成特定的任务或操作。

优点：

1. 提高性能：存储过程是预编译的，执行时无需重新解析和优化，因此可以提高查询效率。
2. 减少网络流量：通过在网络上传输存储过程名称而不是大量SQL代码，减少了客户端与服务器之间的数据传输量。
3. 增强安全性：可以对存储过程设置权限，限制用户直接访问底层表数据。
4. 代码重用：存储过程可以被多次调用，避免重复编写相同的SQL代码。

简单使用

1. 创建两个表

create table student
(
id int identity(1,1),
stuNo varchar(50) primary key,
stuName varchar(50),
stuAge datetime,
stuSex varchar(5)
)
 
create table course
(
id int identity(1,1),
stuNo varchar(50),
courseName varchar(50),
courseScore decimal
)
 
insert into student(stuNo,stuName,stuAge,stuSex) values('01','张三','1996-08-27 09:00:00','男')
insert into student(stuNo,stuName,stuAge,stuSex) values('02','李四','1995-04-27 09:00:00','男')
insert into student(stuNo,stuName,stuAge,stuSex) values('03','王二','1996-03-27 09:00:00','女')
 
insert into course values('01','数学',88),('01','语文',55),('01','英语',67)
insert into course values('02','数学',66),('02','语文',80),('02','英语',78)
insert into course values('03','数学',55),('03','语文',90),('03','英语',72)

 2. 无参数的存储过程

查询学生表的所有信息

--创建存储过程，名为readAllStudent
create proc readAllStudent
as
--查询所有学生信息
select * from student
go
--执行存储过程
exec readAllStudent

3. 有参数的存储过程

查询某个学生的信息

--创建有参数的存储过程
create proc readOneStudent
--创建变量
@stuName varchar(50)
--设置变量默认值
--@stuName varchar(50) = '张三'
as
select * from student where student.stuName = @stuName
go
--执行存储过程
exec readOneStudent '张三'

4. 有输出参数

查询某个学生的性别

--查询单个学生的性别
create proc procQueryStudentAge
@sname varchar(50),
@result varchar(8) output	--输出参数
as
select @result = stuSex from student where @sname=stuName
go
 
 
declare @result varchar(8)    --声明变量
exec procQueryStudentAge '张三',@result output
print @result	--输出

 ADO.NET操作存储过程

1. 创建命令对象

using (SqlCommand command = new SqlCommand("存储过程名称", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    // 添加参数...
    // 执行操作...
}

2. 输入参数

command.Parameters.AddWithValue("@参数名", 参数值);

3. 输出参数

SqlParameter outputParam = new SqlParameter
{
    ParameterName = "@输出参数名",
    SqlDbType = SqlDbType.Int, // 根据实际类型调整
    Direction = ParameterDirection.Output
};
command.Parameters.Add(outputParam);

4. 返回值

SqlParameter returnParam = new SqlParameter
{
    ParameterName = "@ReturnValue",
    SqlDbType = SqlDbType.Int,
    Direction = ParameterDirection.ReturnValue
};
command.Parameters.Add(returnParam);

EF Core执行存储过程

var role = "Admin";
var users = await context.Users
    .FromSqlRaw("EXEC GetUsersByRole @Role", new SqlParameter("@Role", role))
    .ToListAsync();