常用存储过程语法

http://blog.csdn.net/zxcred/archive/2008/09/19/2953653.aspx

前面学过了基本的存储过程,见

  存储过程入门

  现在学一下常用的存储过程的语法，只要花一点点时间学习下，就能用存储过程实现很复杂的功能，可以少写很多代码。

  为了方便说明，数据库使用SQL Server的示例数据库，Northwind和pubs，如果SQL Server中没有的话，可以按下面的方法安装

1，下载SQL2000SampleDb.msi,下载地址是：
http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en 
2，安装后，到默认目录C:\SQL Server 2000 Sample Databases 有instnwnd.sql ，instpubs.sql两个文件 
3，在sql server中运行这两个sql 就可以创建你Northwind和pubs数据库。

  下面开始学T-SQL的语法

  一.注释

   -- 单行注释，从这到本行结束为注释,类似C++,c#中//
  /* … */ 多行注释，类似C++，C#中/* … */

  二.变量（int, smallint, tinyint, decimal,float,real, money ,smallmoney, text ,image, char, varchar。。。。。。）
语法：
DECLARE 
  {
  {@local_variable data_type}
  } [,...n]
例如：

1. declare @ID int --申明一个名为@ID的变量，类型为int型

 

  三.在SQL Server窗口中打印出变量的值

语法：
PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr

  四.变量赋值

 例如：

 

在SQL中，我们不能像代码那样直接给变量赋值，例如@id = 1，如果要达到这样的功能，可以这样写：

1. Declare @ID int
2. Set @ID = (select 1) -- 类似 @ID=1
3. Select @id=1 -- 类似 @ID=1
4. Print @ID

  五.变量运算(+,-,*,/,……)

以下必要时候省略变量申明

1. Set @ID = (select 1+5) --类似 @ID=1+5
2. Set @ID=(select 1-@ID) --类似 @ID=1-@ID

  六.比较操作符
• > (greater than). 
• < (less than). 
• = (equals). 
• <= (less than or equal to). 
• >= (greater than or equal to). 
• != (not equal to). 
• <> (not equal to). 
• !< (not less than). 
• !> (not greater than). 
没什么说的

  七.语句块：Begin … end
将多条语句作为一个块，类似与C++，C#中的{ }
例如：

1. Begin
2.  Set @ID1 = (select 1)
3.  Set @ID2 = (select 2)
4. End

  八.If， if…else…
语法：
IF Boolean_expression
  {sql_statement | statement_block}
[ELSE
  {sql_statement | statement_block}]
例如：

1. If @id is not null
2.   Print ‘@id is not null
3. if @ID = 1
4. begin
5.   Set @ID = (select 1 + 1)
6. end
7. else
8. begin 
9.   set @ID=(select 1+2)
10. end

上面的例子用到了比较操作符，语句块，和IF的语法。

  九.执行其他存储过程 EXEC
例如

1. EXEC dbo.[Sales by Year] @Beginning_Date=’1/01/90’, @Ending_Date=’1/01/08’

 

 

  十.事务

语法：

BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]

例如

1. BEGIN TRAN
2. -- 做某些操作，例如Insert into …
3. if @@error <> 0
4. BEGIN
5.  ROLLBACK TRAN
6. END
7. else
8. BEGIN
9.  COMMIT TRAN
10. END

 

  十一.游标

我们可以在存储过程中用Select语句取出每一行数据进行操作，这就需要用到游标。

语法：
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement

[FOR UPDATE [OF column_name [,...n]]]

例如：

1. DECLARE @au_id varchar(11), @au_fname varchar(20) –申明变量
2. --申明一个游标
3. DECLARE authors_cursor CURSOR FOR 
4. SELECT au_id, au_fname FROM authors 
5. --打开游标
6. OPEN authors_cursor
7. --取出值
8. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname
9. --循环取出游标的值
10. WHILE @@FETCH_STATUS = 0
11. BEGIN
12.   Print @au_id
13.   Print @au_fname
14.   Print ‘ ’
15.   FETCH NEXT FROM authors_cursor 
16.   INTO @au_id, @au_fname
17. END
18. CLOSE authors_cursor –关闭游标
19. DEALLOCATE authors_cursor --释放游标

十二. If Exists (select ...) update ... else insert ...

  很常用的啦，假如数据表中存在某条记录，那么就更新该记录，否则就插入

  我觉得上面的是存储过程常用的一些东东，如果要更深入的了解，更详细的帮助，请参考SQL Server的帮助文档

本文来自
http://blog.csdn.net/zxcred

1.   --从数据表中取出第一行数据的ID,赋值给变量@id，然后打印出来
2. Declare @ID int
3. Set @ID = (select top(1) categoryID from categories)
4. Print @ID

存储过程的创建及应用（实例讲解）

http://www.blogjava.net/yank/archive/2007/04/22/112616.html

一、添加存储过程如下：
create procedure Addemp
  @emp_login varchar(50),
  @password varchar(50),
  @emp_name varchar(50),
  @duty varchar(50),
  @email varchar(50),
  @mobile_phone varchar(50),
  @work_phone varchar(50),
  @jb int
as
insert into emp
(
   emp_login,
   password,
   emp_name,
   duty,
   email,
   mobile_phone,
   work_phone,
   jb
)

values
(
   @emp_login,
   @password,
   @emp_name,

   @duty,
   @email,
   @mobile_phone,
   @work_phone,
   @jb
)
go

添加代码生成
private void Btn_ok_Click(object sender, System.EventArgs e)
    {
      if(Page.IsValid)
       {
  SqlCommand cm=new SqlCommand("AddEmp",cn);//调用存储过程

  cm.CommandType=CommandType.StoredProcedure;//类型转换
                参数的调用
  cm.Parameters.Add(new SqlParameter("@Emp_login",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@password",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@duty",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@Email",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@mobile_tell",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@work_tell",SqlDbType.VarChar,50));
  cm.Parameters.Add(new SqlParameter("@jb",SqlDbType.Int,4));
                参数赋值
  cm.Parameters["@Emp_login"].Value=Tbx_id.Text;
  cm.Parameters["@password"].Value=Tbx_id.Text;
  cm.Parameters["@name"].Value=Tbx_name.Text;
  cm.Parameters["@duty"].Value=duty.SelectedItem.Value;
  cm.Parameters["@Email"].Value=Tbx_Email.Text;    
  cm.Parameters["@mobile_tell"].Value=mobile_tell.Text;
  cm.Parameters["@work_tell"].Value=work_tell.Text;
  cm.Parameters["@jb"].Value=jb.SelectedItem.Value;
                数据更新命令的执行
  cm.Connection.Open();
  try
   {
    cm.ExecuteNonQuery()；//不返回值
    Response.Redirect("Emp.aspx");
    
   }
   catch(SqlException)
   {
    Lbl_note.Text="添加失败";
    Lbl_note.Style["color"]="red";
   }
   cm.Connection.Close();
   }
  }

  private void Btn_cancel_Click(object sender, System.EventArgs e)
  {
     Page.Response.Redirect("addemp.aspx");  
  }
  private void Cv_id_ServerValidate(object source, System.Web.UI.WebControls.ServerValidateEventArgs args)
  {
   cn.Open();
   SqlCommand cm=new SqlCommand("select * from Emp where emp_login=@emp_login",cn);
   cm.Parameters.Add("@emp_login",SqlDbType.Char,10);
   cm.Parameters["@emp_login"].Value=Tbx_id.Text;
   SqlDataReader dr=cm.ExecuteReader();
   if(dr.Read())
   {
    args.IsValid=false;
   }
   else
   {
    args.IsValid=true;
   }
   cn.Close();
  }
二、数据更新
  1,存储过程如下：
create procedure editemp
  @duty varchar(50),
  @email varchar(50),
  @mobile_phone varchar(50),
  @work_phone varchar(50),
  @emp_id int
as
update emp
 
set
  duty=@duty,
  email=@email,
  mobile_phone=@mobile_phone,
  work_phone=@work_phone
where
  emp_id=@emp_id
go
  2、执行代码
 
private void Dgd_user_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   SqlCommand cm=new SqlCommand("EditEmp",cn);//调用存储过程
   cm.CommandType=CommandType.StoredProcedure;//类型转换
   cm.Parameters.Add(new SqlParameter("@duty",SqlDbType.VarChar,50));    
   cm.Parameters.Add(new SqlParameter("@Email",SqlDbType.VarChar,50));   
   cm.Parameters.Add(new SqlParameter("@mobile_tell",SqlDbType.VarChar,50));
   cm.Parameters.Add(new SqlParameter("@work_tell",SqlDbType.VarChar,50));
   cm.Parameters.Add(new SqlParameter("@Emp_id",SqlDbType.Int,4));

   string colvalue=((TextBox)e.Item.Cells[4].Controls[0]).Text;
   cm.Parameters["@duty"].Value=colvalue;

   colvalue=((TextBox)e.Item.Cells[5].Controls[0]).Text;
   cm.Parameters["@Email"].Value=colvalue; 
  
   colvalue=((TextBox)e.Item.Cells[6].Controls[0]).Text;
   cm.Parameters["@mobile_tell"].Value=colvalue;

   colvalue=((TextBox)e.Item.Cells[7].Controls[0]).Text;
   cm.Parameters["@work_tell"].Value=colvalue; 
     
   cm.Parameters["@Emp_id"].Value=Dgd_user.DataKeys[(int)e.Item.ItemIndex];
   cm.Connection.Open();
   try
   {
    cm.ExecuteNonQuery();
    Lbl_note.Text="编辑成功";
    Dgd_user.EditItemIndex=-1;
   }
   catch(SqlException)
   {
    Lbl_note.Text="编辑失败";
    Lbl_note.Style["color"]="red";
   }
   cm.Connection.Close();  
   BindGrid();
  }

三、数据删除
1、存储过程

create procedure deleteemp
 @emp_id int
as
  delete
from
emp
where
  emp_id=@emp_id
go
2、执行代码

private void Dgd_user_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   SqlCommand cm=new SqlCommand("deleteEmp",cn);
   cm.CommandType=CommandType.StoredProcedure;

   cm.Parameters.Add(new SqlParameter("@Emp_id",SqlDbType.Int,4));
   cm.Parameters["@Emp_id"].Value=Dgd_user.DataKeys[(int)e.Item.ItemIndex];
   
   cm.Connection.Open();
   try
   {
    cm.ExecuteNonQuery();
    Lbl_note.Text="删除成功";
    
   }
   catch(SqlException)
   {
    Lbl_note.Text="删除失败";
    Lbl_note.Style["color"]="red";
   }
   cm.Connection.Close();   
   BindGrid();
  }