SQL存储过程基础

什么是存储过程呢？
存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。 
通俗来讲：存储过程其实就是能完成一定操作的一组SQL语句。

那为什么要用存储过程呢？
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

create proc userProc-- 创建一个存储过程
    --这里写参数，如果有的话；没有的话就空着  
    as  
    --这里写具体语句，可以写N个  
    go--可加可不加，go的意思是另起一页，相当于下一个功能块。如果下边不写语句，可以不加！  

无参数存储过程：
选出user表中的所有信息，

create proc userProc
as //此处 as 不可以省略不写
begin //begin 和 end 是一对，不可以只写其中一个，但可以都不写
select userid,username from user
end
go

有参数存储过程：
全局变量
全局变量也称为外部变量，是在函数的外部定义的，它的作用域为从变量定义处开始，到本程序文件的末尾。
选出指定用户名的信息:

create proc userProc
@username varchar(100) 
as 
begin
select userid,username from user where username=@username
end
go

exec userProc '三三' //执行语句

上面是在外部给变量赋值，也可以在内部直接给变量设置默认值

create proc userProc
@username varchar(100)='三三
as 
begin
select userid,username from user where username=@username
end
go

exec userProc

也可以把变量的内容输出，使用output

create proc userProc
@username varchar(100),
@IsRight int output //传出参数
as 
if exists (select userid,username from user where username=@username)
set @IsRight =1
else
set @IsRight=0
go

declare @IsRight int 
exec userProc '三三' , @IsRight output
select @IsRight

以上是全局变量，下面来了解局部变量
局部变量也称为内部变量。局部变量是在函数内作定义说明的。其作用域仅限于函数内部，离开该函数后再使用这种变量是非法的。
局部变量的定义：必须先用Declare命令定以后才可以使用，declare{@变量名 数据类型}
局部变量的赋值方法：set{@变量名=表达式}或者select{@变量名=表达式}
局部变量的显示：select @变量名

create proc userProc
as 
declare @username varchar(100)
set @username='赵雷'
select userid,username from user where username=@username
go

exec userProc

那如果是要把局部变量的数据显示出来怎么办呢？

create proc userProc
as 
declare @username varchar(100)
set @username=(select username from user where userid=1)
select @username
go

exec userProc

 

下面再来讲解如何在C#中调用存储过程。废话不过说，一段完整的代码+注释让你明白一切！这段C#代码和上边的存储过程是完全对应的。

            string strsql = "Data Source=localhost;Initial Catalog=######;Integrated Security=True";//数据库链接字符串  
            string sql = "SelectUserName";//要调用的存储过程名  
            SqlConnection conStr = new SqlConnection(strsql);//SQL数据库连接对象，以数据库链接字符串为参数  
            SqlCommand comStr = new SqlCommand(sql, conStr);//SQL语句执行对象，第一个参数是要执行的语句，第二个是数据库连接对象  
            comStr.CommandType = CommandType.StoredProcedure;//因为要使用的是存储过程，所以设置执行类型为存储过程  
            //依次设定存储过程的参数  
            comStr.Parameters.Add("@Param1", SqlDbType.Text).Value = "####";
            conStr.Open();//打开数据库连接  
          //  MessageBox.Show(comStr.ExecuteNonQuery().ToString());//执行存储过程  
            SqlDataAdapter SqlDataAdapter1 = new SqlDataAdapter(comStr);
            DataTable DT=new DataTable ();
            SqlDataAdapter1.Fill(DT);
            dataGridView1.DataSource = DT;
            conStr.Close();//关闭连接  

//添加数据库引用
using System.Data.SqlClient;
......
 private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   String DBConnStr;
   DataSet MyDataSet=new DataSet();
   System.Data.SqlClient.SqlDataAdapter DataAdapter=new System.Data.SqlClient.SqlDataAdapter();
   DBConnStr=System.Configuration.ConfigurationSettings.AppSettings["ConnectString"];
   System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(DBConnStr);
   if (myConnection.State!=ConnectionState.Open) 
   {
    myConnection.Open();
   }   
   System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand("P_Test",myConnection);
   myCommand.CommandType=CommandType.StoredProcedure;
   //添加输入查询参数、赋予值
   myCommand.Parameters.Add("@Name",SqlDbType.VarChar);
   myCommand.Parameters["@Name"].Value ="A";

   //添加输出参数
   myCommand.Parameters.Add("@Rowcount",SqlDbType.Int);
   myCommand.Parameters["@Rowcount"].Direction=ParameterDirection.Output;


   myCommand.ExecuteNonQuery();
   DataAdapter.SelectCommand = myCommand;

   if (MyDataSet!=null)
   {
     DataAdapter.Fill(MyDataSet,"table");
   }
   
   DataGrid1.DataSource=MyDataSet;
   DataGrid1.DataBind();
   //得到存储过程输出参数
   Label1.Text=myCommand.Parameters["@Rowcount"].Value.ToString();

   if (myConnection.State == ConnectionState.Open)
   {
    myConnection.Close();
   }

  }