sql預存程序簡單教程

①為什麼要使用預存程序?
因為它比SQL語句執行快.

②預存程序是什麼?
把一堆SQL語句羅在一起,還可以根據條件執行不通SQL語句.(AX寫作本文時觀點)

③來一個最簡單的預存程序
CREATE PROCEDURE dbo.testProcedure_AX
AS
select userID from USERS order by userid desc

注:dbo.testProcedure_AX是你建立的預存程序名,可以改為:AXzhz等,別跟關鍵字衝突就行了.AS下面就是一條SQL語句,不會寫SQL語句的請迴避.

④我怎麼在Asp.Net中調用這個預存程序?
下面黃底的這兩行就夠使了.
         public static string GetCustomerCName(ref ArrayList arrayCName,ref ArrayList arrayID)
         {
             SqlConnection con=ADConnection.createConnection();
             SqlCommand cmd=new SqlCommand("testProcedure_AX",con);
             cmd.CommandType=CommandType.StoredProcedure;
             con.Open();
             try
             {
                 SqlDataReader dr=cmd.ExecuteReader();
                 while(dr.Read())
                 {
                     if(dr[0].ToString()=="")
                     {
                         arrayCName.Add(dr[1].ToString());
                     }
                 }
                 con.Close();
                 return "OK!";
             }
             catch(Exception ex)
             {
                 con.Close();
                 return ex.ToString();
             }
   }
注:其實就是把以前
SqlCommand cmd=new SqlCommand("select userID from USERS order by userid desc",con);
中的SQL語句替換為預存程序名,再把cmd的類型標註為CommandType.StoredProcedure(預存程序)

⑤寫個帶參數的預存程序吧,上面這個簡單得有點慘不忍睹,不過還是蠻實用的.
參數帶就帶兩,一個的沒面子,太小家子氣了.

CREATE PROCEDURE dbo.AXzhz
/*
這裡寫注釋
*/
@startDate varchar(16),
@endDate varchar(16)
AS
select id   from table_AX where commentDateTime>@startDate and commentDateTime<@endDate order by contentownerid DESC

注:@startDate varchar(16)是聲明@startDate 這個變數,多個變數名間用【,】隔開.後面的SQL就可以使用這個變數了.

⑥我怎麼在ASP.NET中調用這個帶參數的預存程序?

public static string GetCustomerCNameCount(string startDate,string endDate,ref DataSet ds)
{
             SqlConnection con=ADConnection.createConnection();
//-----------------------注意這一段--------------------------------------------------------------------------------------------------------
             SqlDataAdapter da=new SqlDataAdapter("AXzhz",con);

             para0=new SqlParameter("@startDate",startDate);
             para1=new SqlParameter("@endDate",endDate);
             da.SelectCommand.Parameters.Add(para0);
             da.SelectCommand.Parameters.Add(para1);
             da.SelectCommand.CommandType=CommandType.StoredProcedure;
//-------------------------------------------------------------------------------------------------------------------------------

             try
             {
                 con.Open();
                 da.Fill(ds);
                 con.Close();
                 return "OK";
             }
             catch(Exception ex)
             {
                 return ex.ToString();
             }           
}

注:把命令的參數添加進去,就OK了

⑦我還想看看SQL命令執行成功了沒有.
注意看下面三行紅色的語句

CREATE PROCEDURE dbo.AXzhz
/*
   @parameter1 使用者名稱
   @parameter2 新密碼
*/
@passWord nvarchar(20),
@userName nvarchar(20)
AS
declare @err0 int
update WL_user set password=@password where UserName=@userName
set @err0=@@error
select   @err0 as err0

注:先聲明一個整型變數@err0,再給其賦值為@@error(這個是系統自動給出的語句是否執行成功,0為成功,其它為失敗),最後通過select把它選擇出來,某位高人說可以通過Return返回,超出本人的認知範圍,俺暫時不會,以後再補充吧

⑧那怎麼從後台獲得這個執行成功與否的值呢?
下面這段代碼可以告訴你答案:
     public static string GetCustomerCName()
         {
             SqlConnection con=ADConnection.createConnection();
             SqlCommand cmd=new SqlCommand("AXzhz",con);
             cmd.CommandType=CommandType.StoredProcedure;
             para0=new SqlParameter("@startDate","2006-9-10");
             para1=new SqlParameter("@endDate","2006-9-20");
             da.SelectCommand.Parameters.Add(para0);
             da.SelectCommand.Parameters.Add(para1);
             con.Open();
             try
             {
                Int32 re=(int32)cmd.ExecuteScalar();
                 con.Close();
                 if (re==0)
                  return "OK!";
                 else
                  return "false";
             }
             catch(Exception ex)
             {
      con.Close();
                 return ex.ToString();
             }
         }
注:就是通過SqlCommand的ExecuteScalar()方法取回這個值,這句話是從MSDN上找的,俺認為改成:
      int re=(int)cmd.ExecuteScalar();   99%正確,現在沒時間驗證,期待您的測試!!!

1)執行一個沒有參數的預存程序的代碼如下：

SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcedure";
da.selectCommand.CommandType = CommandType.StoredProcedure;
(2)執行一個有參數的預存程序的代碼如下

SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcedure";
da.selectCommand.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Input;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);
若需要添加輸出參數：

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Output;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);
若要獲得參儲過程的傳回值：

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.ReturnValue;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

兩種不同的預存程序調用方法

為了突出新方法的優點，首先介紹一下在.NET中調用預存程序的“官方”方法。另外，本文的所有樣本程式均工作於SqlServer資料庫上，其它情況類似，以後不再一一說明。本文所有例子均採用C#語言。

要在應用程式中訪問資料庫，一般性的步驟是：首先聲明一個資料庫連接SqlConnection，然後聲明一個資料庫命令SqlCommand，用來執行SQL語句和預存程序。有了這兩個對象後，就可以根據自己的需要採用不同的執行方式達到目的。需要補充的是，不要忘記在頁面上添加如下的引用語句：using System.Data.SqlClient。

就執行預存程序來說，如果執行的是第一類預存程序，那麼就要用一個DataAdapter將結果填充到一個DataSet中，然後就可以使用資料格控制項將結果呈現在頁面上了；如果執行的是第二和第三種預存程序，則不需要此過程，只需要根據特定的返回判定操作是否成功完成即可。

(1)執行一個沒有參數的預存程序的代碼如下：

SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcedure";
da.selectCommand.CommandType = CommandType.StoredProcedure;

然後只要選擇適當的方式執行此處過程，用於不同的目的即可。

(2)執行一個有參數的預存程序的代碼如下（我們可以將調用預存程序的函式宣告為ExeProcedure(string inputdate)）：

SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcedure";
da.selectCommand.CommandType = CommandType.StoredProcedure;
（以上代碼相同，以下為要添加的代碼）
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Input;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

這樣就添加了一個輸入參數。若需要添加輸出參數：

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Output;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

若要獲得參儲過程的傳回值：

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.ReturnValue;
param.Value = Convert.ToDateTime(inputdate);
da.selectCommand.Parameters.Add(param);

從上面的代碼我們可以看出，當預存程序比較多或者預存程序的參數比較多時，這種方法會大大影響開發的速度；另外一方面，如果項目比較大，那麼這些用於資料庫邏輯的函數在以後的維護中也是一個很大的負擔。那麼，有沒有一種改進的方法可以解決這個問題呢？想到在執行沒有參數的預存程序時只需要傳入一個預存程序的名字就可以調用相應的預存程序，而且在SqlServer資料庫中我們可以直接在查詢分析器中敲入“預存程序名（參數列表）”樣的字串就可以執行預存程序，那麼，是否可以把這種思想應用到應用程式中呢？

於是在編譯器中鍵入相應代碼。這些代碼是在調用不帶參數的預存程序的代碼的基礎上改的。具體代碼如下：

SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.selectCommand = new SqlCommand();
da.selectCommand.Connection = conn;
da.selectCommand.CommandText = "NameOfProcedure（'para1','para2',para3）";
da.selectCommand.CommandType = CommandType.StoredProcedure;
預存程序的方法，使我在添加資料中走了不少的彎路，最近，在查閱了大量的資料之後，終於在微軟的一個執行個體中找到了一種良好的方法。
首先編寫好一有傳回值的預存程序
create procedure proc_name
   @para1 nchar(20),    --輸入參數
   @para2 int = null out　--輸出參數，供程式使用
as
   set nocount on
   if ( not exists (select * from employee where em_name=@para1))
   begin
       insert into employee(name) values(@para1)  
       select @para2=@@identity      --返回添加記錄的ID
       return 1                                --返回是否成功添加資料
字串6

   end
   else
      return 0                               --返回失敗
go
然後是調用預存程序的方法
sqlcommand command;
command = new sqlcommand(proc_name,new sqlconnection(connectionstr));
command.paraments.add("@para1"),"name1");  //輸入參數，職員姓名
command.paraments.add(new sqlparament("@para2",   //產生一輸出參數
SqlDbType.Int;　　　　　　　　　　　　　//參數資料類型
ParamenterDirection.OutPut,　　　　　　//輸入輸出類型
0,
0,
string.Emplty,

用SqlCommand和DataSet:
SqlConnection conn=new SqlConnection("server=(local);uid=;password=;database=");
SqlCommand cmd=new SqlCommand("StoreProcedure",connn);
cmd.CommandType=CommandType.StoreProcedure;

SqlDataAdapter dsCommand=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
dsCommand.Fill(ds);

2.用SqlCommand和SqlDataAdapter
Sqlconnection conn=new SqlConnection("server=(local);uid=;password=;database=");
SqlCommand cmd=new SqlCommand("StoreProcedure",conn);
cmd.CommandType=CommandType.StoreProcedure;
SqlDataReader dr=cmd.ExecuteReader()
while(dr.Read())
{
   Response.Write(dr.Item["Field"]);
}

DataRowVerstion.Default,
null)　　　　　　　　　　　　　　　　　//參數值，輸入參數時需提供
);
command.commandtype=commandtype.StoredProcedure;
command.connection.open();
command.executenonQuery();
int pkid=(int)command.Parameters["@para2"].value;  //得到輸出參數的值
command.connection.close(); 字串6
此處是引用輸出參數，如果要引用傳回值（是否成功添加資料）則只需把ParamenterDirection的類型改為returnvalue;再自己改一個參數名就可以了.

 

出處：http://www.cnblogs.com/jayleke/archive/2010/07/10/1774746.html