SQLServer存储过程和ADO.NET访问存储过程整理 java程序员

转自：http://www.cnblogs.com/qq731109249/archive/2012/10/07/2714236.html

SqlServer存储过程的声明方式：

Create  proc存储过程名称

As

T-sql命令

SqlServer存储过程分为无参数存储过程和有参数存储过程

1）没有参数的存储过程

调用存储过程

Exec  存储过程名称；

这样的用法和视图差不多，但是视图不可以使用order by命令。而存储过程没有这样的限制。

示例1-1代码如下：

 --创建不带参数的存储过程
 create proc select_activity
 as
 --该存储过程所执行的sql命令
 select * from T_Activity;

SQL调用该存储过程

--调用select_activity存储过程
 exec select_activity

有参数的存储过程

主要有三种类型的参数：

1）输入参数2）输出参数3）返回值

输入参数即在调用这个存储过程之前必须输入参数而该参数仅在程序中使用，并不会再返回给用户

基本的声明方式：

Create proc  存储过程名称

@输入参数名称1  数据类型，

@输入参数名称2  数据类型

As

T-sql命令

如果存在多个参数，就需要在各个输入参数间以逗号分隔 。如果参数是字符串或者日期，则需要用单引号括起来，而数字则不用。

备注：

存储过程通常不要编写太长，因为存储过程是在数据库端执行的，太长的话会影响执行效率；

示例1-2代码如下：

--创建带参数的存储过程
 create proc select_activity1
 
 --修改存储过程的语句
 --alter proc select_activity1
 
 --声明参数，多个参数有“,”号分隔符隔开，参数后面声明该参数的数据类型
 @id int,
 @activityName nvarchar(50)
 as
 --该存储过程所执行的Sql命令
 select * from T_Activity where id=@id and activityName=@activityName;
 
 --调用带参数的存储过程
 --如果参数是字符串或者日期，则需要用单引号括起来，而数字则不用。
 exec select_activity1 5,'比武招亲'
 
 --或者
 exec select_activity1
 @id=1,
 @activityName='比武招新';

存储过程中可以包含一些简单的编程语句

比如：if ，begin  ·····end····等语句

示例1-3代码如下：

--在设置参数时，如果需要给设置的参数赋值，则在声明的参数后面加“=”后面跟所设置的初始值，这里设置的初始值为空
 create proc sele_activity
 @id int=null,
 @activityInfoId int=null
 as
 
 --使用的if语句来判断输入的参数值是否为空，如果为空则返回前端错误信息“请输入参数”，而且使用return立刻结束该存储过程
 if @id is null or @activityInfoId is null
     begin
         raiserror('请输入参数',16,1)
         return
         end
 --执行的sql命令
 select * from T_Activity where id=@id or activityInfoId=@activityInfoId;
 
 --使用了没有给参数赋值的存储过程，返回错误
 --错误信息：“消息 50000，级别 16，状态 1，过程 sele_activity，第 7 行 请输入参数”
 exec sele_activity;
 
 --这样调用返回正确的结果
 exec sele_activity
 @id=2,
 @activityInfoId=8;

输出参数的存储过程

输出参数和输入参数刚好相反，输入参数只会在as一下活动，而输出参数则返回前端使用

输出参数的声明过程：

Createproc存储过程名称

@输入参数名称   数据类型

@输出参数名称   数据类型  output

As

T-Sql命令

在声明输出参数变量时，必须声明为output，存储过程执行完成以后会将输出参数返回前端

示例1-4代码如下：

--创建select_MathResult的存储过程
 create proc select_MathResult
 --声明输出参数@activityName和参数的类型
 @activityName nvarchar(50),
 --声明输出参数,并使用output来表明该参数为输出参数
 @result int output
 
 as
 --使用T-SQL语句来返回相同activityName的数据行数
 select @result=count(*) from T_Activity where activityName=@activityName
 
 --调用存储过程，调用时先声明接受输出参数的参数@answer和该参数的类型
 declare @answer int
 exec select_MathResult '比武招亲' ,@answer output
 
 select '结果是',@answer

使用ADO,NET来调用存储过程可以分为一下4种方式

1）调用不带参数的存储过程

2）调用带一个参数的存储过程

3）调用带多个参数的存储过程

4）调用带输出参数的存储过程

一，ADO.NET调用不带参数的存储过程，使用的存储过程是上篇博文示例1-1创建的存储过程

具体的调用代码1-1演示如下：

public void ShowData1()
         {
             SqlConnection con = new SqlConnection(strConString);
             //实例化SqlCommand对象，参数对象指定为所要访问存储过程的名称
             SqlCommand cmd = new SqlCommand("select_activity", con);
             //指定命令类型为存储过程
             cmd.CommandType = CommandType.StoredProcedure;
             //创建数据集
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             DataTable table = new DataTable();
             adapter.Fill(table);
 
             DataRow Dr = table.Rows[0];
             Response.Write(Dr[3].ToString());
 
         }

二，因为上篇博文没有创建只有一个参数的存储过程，创建过程如下：

--创建带一个参数的存储过程
 create proc select_activity3
 @id int
 as
 --该存储过程所执行的sql命令
 select * from T_Activity where id=@id;
 
 --调用select_activity存储过程
 exec select_activity3 5

ADO.NET调用带一个参数的存储过程的示例1-2代码如下：

/// <summary>
         /// 调用带一个参数的存数过程
         /// </summary>
         public void ShowData2()
         {
             SqlConnection con = new SqlConnection(strConString);
             SqlCommand cmd = new SqlCommand("select_activity3", con);
             //指定命令类型为存储过程
             cmd.CommandType = CommandType.StoredProcedure;
 
             //声明参数对象
             SqlParameter parameter = new SqlParameter();
             //指定参数名
             parameter.ParameterName = "@id";
             //指定参数的数据类型
             parameter.SqlDbType = SqlDbType.Int;
             //给参数赋值
             parameter.Value = 5;
             //将命令对象加入命令对象的参数集合中
             cmd.Parameters.Add(parameter);
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             //创建DataTable对象
             DataTable table = new DataTable();
             adapter.Fill(table);
 
             DataRow Dr = table.Rows[0];
             Response.Write(Dr[3].ToString());
 
         }

三，ADO.NET调用带多个参数的存储过程，本示例1-3代码以两个参数的作为演示，更多的参数，可以参照该示例，使用的存储过程为上篇博文的示例1-2

/// <summary>
         /// 调用带两个参数的参数数组
         /// </summary>
         public void ShowData3()
         {
             SqlConnection con = new SqlConnection(strConString);
             SqlCommand cmd = new SqlCommand("select_activity1", con);
             //指定命令类型为存储过程
             cmd.CommandType = CommandType.StoredProcedure;
 
             //声明一个多维的参数数组
             SqlParameter[] parameter = 
             {
                 new SqlParameter("@id",SqlDbType.Int,4),
                 new SqlParameter("@activityName",SqlDbType.NVarChar,50)
             };
             parameter[0].Value = 5;
             //给参数赋值
             parameter[1].Value = "比武招亲";
             //将命令对象加入命令对象的参数集合中
             cmd.Parameters.AddRange(parameter);
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             //创建DataTable对象
             DataTable table = new DataTable();
             adapter.Fill(table);
 
             DataRow Dr = table.Rows[0];
             Response.Write(Dr[3].ToString());
         }

四，ADO.NET访问带输出参数的存储过程，本示例1-4代码以输入一个且输出一个参数的作为演示，更多的参数，可以参照该示例，使用的存储过程为上篇博文的示例1-4

/// <summary>
         /// 调用带返回值的存储过程
         /// </summary>
         public void ShowData4()
         {
             SqlConnection con = new SqlConnection(strConString);
             SqlCommand cmd = new SqlCommand("select_MathResult", con);
             cmd.CommandType = CommandType.StoredProcedure;
             SqlParameter[] parameter = {
                                            new SqlParameter("@activityName",SqlDbType.NVarChar,50),
                                            new SqlParameter("@result",SqlDbType.Int,4)
                                        };
 
             parameter[0].Value = "比武招亲";
             //设定输出参数的输出方法向
             parameter[1].Direction = ParameterDirection.Output;
             cmd.Parameters.AddRange(parameter);
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             DataTable table = new DataTable();
             adapter.Fill(table);
             Response.Write(Convert.ToInt32(cmd.Parameters[1].Value));
         }