sql存储过程的简单使用

存储过程（Stored Procedure）是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

创建存储过程的基本代码结构

CREATE PROCEDURE Procedure_Name  

    --Procedure_Name为存储过程名(不能以阿拉伯数字开头)，在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。

    @Param1 Datatype,@Param2 Datatype 

    --@Param1和@Param2为存储过程的参数，Datatype为参数类型,多个参数用逗号隔开,最多允许个参数。

AS --存储过程要执行的操作 

BEGIN

    --BEGIN跟END组成一个代码块，可以写也可以不写，如果存储过程中执行的SQL语句比较复杂，用BEGIN和END会让代码更加整齐，更容易理解。

END
GO --GO就代表结操作完毕　　

exec Procedure_Name [参数名] --调用存储过程Procedure_Name。

drop procedure Procedure_Name --删除存储过程Procedure_Name，不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息

exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本

　　下面两个小例子

1.首先建立这样的一个库和表

2.简单查询不带参数。

存储过程创建
create procedure sp_p1
as
select * from people

Sql调用
exec sp_p1

C#调用
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Server=PC201312290054\SQLEXPRESS;database=Test;uid=sa;pwd=sa";//连接数据库
            conn.Open();
            SqlCommand cmd = new SqlCommand("sp_p1", conn);//其中Proc为存储过程名称
            cmd.CommandType = CommandType.StoredProcedure;//指定执行类型为存储过程
            DataTable dt = new DataTable();
            //执行存储过程
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //将结果填充到datatable中
            sda.Fill(dt);
            //return dt;//返回Datatable
            dataGridView1.DataSource = dt;

　　

3.简单查询带参数

创建
create procedure sp_p2
@id int
as
select * from people where id=@id

Sql 调用
exec sp_p2 1

C# 调用

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Server=PC201312290054\SQLEXPRESS;database=Test;uid=sa;pwd=sa";//连接数据库
            conn.Open();
            SqlCommand cmd = new SqlCommand("sp_p2", conn);//其中Proc为存储过程名称
            cmd.CommandType = CommandType.StoredProcedure;//指定执行类型为存储过程
            cmd.Parameters.Add(new SqlParameter("@id", 1));//传递参数
            DataTable dt = new DataTable();
            //执行存储过程
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //将结果填充到datatable中
            sda.Fill(dt);
            dataGridView1.DataSource = dt;

4.常用系统存储过程

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

5.优点

　　1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

　　2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

　　3.存储过程可以重复使用,可减少数据库开发人员的工作量(复用性高，面向对象的编程思想)

　　4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

缺点

　　1.调试麻烦。

　　2.移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

　　3.重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

　　4.如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。