T-SQL 之 存储过程入门

 

  当你还在使用拼接 T-SQL 语句让后通过 SqlCommand 执行它，是否感觉过的很麻烦？或许T-SQL语句很简单，也就花费不了多少时间。如果T-SQL语句长达5行以上你是否会感觉厌恶、频繁出错呢？而今天就让我们去学习一个可以大大减少我们在VS中拼写T-SQL语句的方法。就在是学习如何编写和使用存储过程。

  所要具备的条件:

1.    学习过 ADO.NET
2.    学习过 T-SQL 基本语句
3.    使用NORTHWND数据库作为举例

 

  一、什么是存储过程

    很多权威的说法就是‘允许重复执行某个任何的SQL语句。只要创建某个过程一次，就可以在程序中多次重用它。这一稿了程序的可维护性，允许程序以统一、优化的方式访问数据库。’，当然我不知道大家有多少人可以完全的理解,所以这里我就是用比较简单的方式来说明（只是个人暂时的理解）: 就是一种写好的T-SQL语句，但是却封装了这些T-SQL语句，跟函数一样，可以允许我们传入参数，返回参数（如果有记录集还是一样返回）。只是当我们使用的是查询语句时比一般的函数多返回一个记录集，其他的完全跟函数一样。

   优点:

1.    在 SQL SERVER 中编写,提示更佳
2.    在 SQL SERVER 中可以立即进行测试
3.    因为 存储过程 存储在数据库中并经过优化，执行速度更快
4.    大大节省我们在VS中编写T-SQL语句的时间

  缺点:

1.    如果要修改,必须进入到数据库中修改
2.    需要记住每个存储过程的调用名、参数、返回值以及功能

  什么技术都会有缺点和优点,除非你的功能很简单，否则选择使用存储过程优点还是很大的.我们可以单独写个各个存储过程的说明

 

 二、了解基本操作

1. 如何声明一个无参数无返回值的存储过程
   

   CREATE PROCEDURE sp_Select_All_Employees
   AS
        SELECT employeeid,firstname,lastname
        FROM Employees
        ORDER BY lastname , firstname

   各部分说明如下:

   CREATE PROCEDURE sp_Select_All_Employees
   
   
   /*
   
   创建一个名为 sp_Select_All_Employees 的存储过程
   
   */

   AS
       SELECT employeeid , firstname , lastname
       FROM employees
       ORDER BY lastname , firstname
   
   /*
      AS 后面为实现该存储过程的功能语句
   */

    

2. 创建一个带有一个参数的存储过程
   

   CREATE PROCEDURE sp_Orders_By_EmployeeId
          @employeeid int
   AS
        SELECT orderid , customerid
        FROM orders
        WHERE employeeid = @employeeid
   
   
   /*
       其中 @employeeid 为输入参数，且后面为该参数类型
   */

    

3. 创建带有一个传入参数、返回值和输出参数的存储过程
   

   CREATE PROCEDURE sp_Orders_By_EmployeeId2
          @employeeid int,
          @ordercount int = 0 output
   AS
       SELECT orderid,customerid
       FROM orders
       WHERE employeeid = @employeeid;
       SELECT @ordercount = count(*)
       FROM orders
       WHERE employeeid = @employeeid
       return @ordercount

   各部分说明如下:

   @ordercount int = 0 output
   --表示 @ordercount 变量为 int 类型,默认值为 0 且为输出参数
   
   return @ordercount
   --表示 @ordercount 为返回值

    

4. 修改存储过程
   

   ALTER PROCEDURE --存储程序名称
      --改变的参数
   AS
       --改变后的T-SQL语句

    

5. 查看存储过程
   

   execute sp_helptext --存储过程名称

    

6. 重命名存储过程
   

   EXECUTE sp_rename --需要改的存储过程名称  --改后的存储过程名称

    

7. 调用存储过程
   这个调用和一般的调用函数一样，只是需要在存储过程名称前加EXECUTE，其次没有括号
   类似如下:

EXECUTE sp_Orders_By_EmployeeId2 2 @value

  三、在C#中使用存储过程

    1.调用 sp_Select_All_Employees

SqlConnection con = new SqlConnection('/*数据库连接字符串*/');
try
{
      con.Open();
      SqlCommand cmd = con.CreateCommand();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = "sp_Select_All_Employees";
      SqlDataReader reader = cmd.ExecuteReader();
     /*
          输出数据或者下断点看
     */
}

    2.调用 sp_Orders_By_EmployeeId2
  

SqlConnection con = new SqlConnection("/*数据库连接字符串*/");
try
{
     con.Open();
     SqlCommand cmd = con.CreateCommand();
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.CommandText = "sp_Orders_By_EmployeeId2";
     SqlParameter inparm = cmd.Parameters.Add("@employeeid",SqlDbType.Int);
     inparm.Direction = ParameterDirection.Input;
     inparm.Value = 2;
     SqlParameter outparm = cmd.Parameters.Add(
     "@ordercount",SqlDbType.Int);
     outparm.Direction = ParameterDirection.Output;
     SqlParameter retval = cmd.Parameters.Add(
     "return_value",SqlDbType.Int);
     retval.Direction = ParameterDirection.ReturnValue;
     SqlDataReader reader = cmd.ExecuteReader(); 
     /*
        同上
     */
}