Sqlserver中的储存过程 - 捞月亮的猴子

一、什么是存储过程（Stored Procedure）

存储过程是一段存储在数据库的“子程序”，本质是一个可重复使用的SQL代码块，可以理解为数据库端的“方法”。

存储过程的好处：

①提高性能：由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。

②提高通信速率：网络通信中传输的内容是存储过程名字，相比传输大量的sql语句网络的要通信量小，提高通信速率。

③提高安全性能：存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

二、Sql Server使用存储过程

例子使用的UserInfo表只有 UserName,UserPass,Email和主键 Id 列

2.1 简单的无参查询（查询用户名和密码）

--创建查询用户名和密码的存储过程
create proc pro_getUserList
select username,userpass from UserInfo 
--执行
exec pro_getUserList

在C#中调用存储过程的代码

using (SqlConnection conn = new SqlConnection(connStr))
                SqlDataAdapter adapter = new SqlDataAdapter("pro_getUserList", conn);
                //设置CommandType
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                //结果集存入dt中
                adapter.Fill(dt);
                //遍历显示结果集
                foreach (DataRow row in dt.Rows)
                    Console.WriteLine(row["username"]+"---"+row["userpass"]);
                Console.ReadKey();
2.2  有返回值的简单插入用户（插入一条新纪录，返回受影响的行数）
--创建名为InsertUserInfo的存储过程
create proc InsertUserInfo
insert into userinfo (username,userpass,email) values ('newuser','123','123@qq.com') 
return @@rowcount
--执行存储过程
exec InsertUserInfo
在C#中调用存储过程的代码
 1  using (SqlConnection conn = new SqlConnection(connStr))
 3                 using (SqlCommand com=new SqlCommand("pro_insertUserInfo",conn))
 5                     conn.Open();
 6                     com.CommandType = CommandType.StoredProcedure;//设置CommandType
 7                     //创建一个接受返回值的参数，设置该参数是返回值类型
 8                     SqlParameter par = new SqlParameter("count", SqlDbType.Int);
 9                     par.Direction = ParameterDirection.ReturnValue;
10                     com.Parameters.Add(par);
12                     int comResult = com.ExecuteNonQuery();//com.ExecuteNonQuery返回受影响的行数 1
13                     Console.WriteLine(comResult);
14                     Console.WriteLine(par.Value.ToString());//通过返回值获取受影响的行数 1
15                     Console.ReadKey();
16                 }
2.3  有输入、输出、返回值的简单查询
　　一个简单栗子：输入省份名查询该省的城市列表，out返回总的城市数目，retrun返回总的省份数目，栗子只是演示存储过程的结果接收方式，并没有太大的实际意义。
　　注意一点 return参数必须是int类型的
CREATE PROCEDURE [dbo].[proc_select_cities_by_provice]
    @provicename nvarchar(50),
    @citycount int out
BEGIN
    SET NOCOUNT ON;
    --根据输入参数proviceName查询城市列表
    SELECT c.id cid,c.Name cname,p.Id pid,p.Name pname
    from Cities c join Provices p on c.ProviceId=p.Id where p.Name=@provicename;
    --out返回城市的总数
    select @citycount=count(1) from Cities;
    declare @provicecount int;
    select @provicecount=count(1) from Provices;
    --返回省份的总数
    return @provicecount;
在C#中调用存储过程的代码
        static void Main(string[] args)
            string connstr = "your sqlconnstring";
            using (SqlConnection conn = new SqlConnection(connstr))
                conn.Open();
                using (SqlCommand com = conn.CreateCommand())
                    com.CommandText = "proc_select_cities_by_provice";
                    com.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] pars = new SqlParameter[]
                     new SqlParameter("@provicename",System.Data.SqlDbType.NVarChar,50){ Value="河南"},
                     new SqlParameter("@citycount",System.Data.SqlDbType.Int){Direction=System.Data.ParameterDirection.Output},
                     new SqlParameter("@provicecount",System.Data.SqlDbType.Int){Direction=System.Data.ParameterDirection.ReturnValue}
                    com.Parameters.AddRange(pars);
                    //接收城市列表
                    using (SqlDataReader reader = com.ExecuteReader())
                        while (reader.Read())
                            Console.WriteLine(
                                $"cid:{reader["cid"].ToString()}," +
                                $"cname:{reader["cname"].ToString()}," +
                                $"pid:{reader["pid"].ToString()}," +
                                $"pname:{reader["pname"].ToString()}");
                    //out参数输出
                    Console.WriteLine($"out:{com.Parameters["@citycount"].Value.ToString()}");
                    //return参数输出
                    Console.WriteLine($"out:{com.Parameters["@provicecount"].Value.ToString()}");
2.4  返回多个结果集
--返回多个结果集
create proc pro_GetLists
select * from userInfo                    --取所有信息
select username,userpass from UserInfo  --只取用户名和密码
--执行
exec pro_GetLists
C#调用储存过程
 using (SqlConnection conn = new SqlConnection(connStr))
                SqlDataAdapter adapter = new SqlDataAdapter("pro_GetLists", conn);
                DataSet ds = new DataSet();
                //ds中是所有的结果
                adapter.Fill(ds);
                DataTable dt0=ds.Tables[0];//第一个select的结果集
                DataTable dt1 = ds.Tables[1];//第二个select的结果集               
备注：在存储过程中return只能返回int类型，out(output)可以返回多种类型,执行到return的时候存储过程即结束,而out的变量可以重复设置。
　　   存储过程中的return和out参数值，在C#中都是通过参数来接收的，select的结果集可以用DataTable或者DataSet进行接收。
本文参考：
http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html
https://blog.csdn.net/lengxiao1993/article/details/53427266
http://www.cnblogs.com/aabbcc/p/6626372.html