TSQL 之存储过程入门 yz

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

所要具备的条件:

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

一、什么是存储过程

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

优点:

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

缺点:

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

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

二、了解基本操作

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

View Code

1 CREATE PROCEDURE sp_Select_All_Employees
2 AS
3      SELECT employeeid,firstname,lastname
4      FROM Employees
5      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 后面为实现该存储过程的功能语句
*/

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

View Code

 1 CREATE PROCEDURE sp_Orders_By_EmployeeId
 2        @employeeid int
 3 AS
 4      SELECT orderid , customerid
 5      FROM orders
 6      WHERE employeeid = @employeeid
 7 
 8 
 9 /*
10     其中 @employeeid 为输入参数，且后面为该参数类型
11 */

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

View Code

 1 CREATE PROCEDURE sp_Orders_By_EmployeeId2
 2        @employeeid int,
 3        @ordercount int = 0 output
 4 AS
 5     SELECT orderid,customerid
 6     FROM orders
 7     WHERE employeeid = @employeeid;
 8     SELECT @ordercount = count(*)
 9     FROM orders
10     WHERE employeeid = @employeeid
11     return @ordercount

各部分说明如下:

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

修改存储过程

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

查看存储过程

execute sp_helptext --存储过程名称

重命名存储过程

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

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

EXECUTE sp_Orders_By_EmployeeId2 2 @value

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

1.调用 sp_Select_All_Employees

 1 SqlConnection con = new SqlConnection('/*数据库连接字符串*/');
 2 try
 3 {
 4       con.Open();
 5       SqlCommand cmd = con.CreateCommand();
 6       cmd.CommandType = CommandType.StoredProcedure;
 7       cmd.CommandText = "sp_Select_All_Employees";
 8       SqlDataReader reader = cmd.ExecuteReader();
 9      /*
10           输出数据或者下断点看
11      */
12 }

2.调用 sp_Orders_By_EmployeeId2

 1 SqlConnection con = new SqlConnection("/*数据库连接字符串*/");
 2 try
 3 {
 4      con.Open();
 5      SqlCommand cmd = con.CreateCommand();
 6      cmd.CommandType = CommandType.StoredProcedure;
 7      cmd.CommandText = "sp_Orders_By_EmployeeId2";
 8      SqlParameter inparm = cmd.Parameters.Add("@employeeid",SqlDbType.Int);
 9      inparm.Direction = ParameterDirection.Input;
10      inparm.Value = 2;
11      SqlParameter outparm = cmd.Parameters.Add(
12      "@ordercount",SqlDbType.Int);
13      outparm.Direction = ParameterDirection.Output;
14      SqlParameter retval = cmd.Parameters.Add(
15      "return_value",SqlDbType.Int);
16      retval.Direction = ParameterDirection.ReturnValue;
17      SqlDataReader reader = cmd.ExecuteReader(); 
18      /*
19         同上
20      */
21 }

TSQL 之 存储过程入门 yz

TSQL 之存储过程入门 yz