asp.net mssql預存程序輸出參數的調用

asp教程.net mssql預存程序輸出參數的調用

最簡單的文法

create proc p
as
  select * from tb

sql預存程序的概念，優點及文法

　　整理在學習程式過程之前，先瞭解下什麼是預存程序?為什麼要用預存程序，他有那些優點

　　定義：將常用的或很複雜的工作，預先用sql語句寫好並用一個指定的名稱儲存起來, 那麼以後要叫資料庫教程提供與已定義好的預存程序的功能相同的服務時,只需調用execute,即可自動完成命令。

　　講到這裡,可能有人要問：這麼說預存程序就是一堆sql語句而已啊? microsoft公司為什麼還要添加這個技術呢?

　　那麼預存程序與一般的sql語句有什麼區別呢?

　　預存程序的優點：

　　1.預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般sql語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。

　　2.當對資料庫進行複雜操作時(如對多個表進行update,insert,query,delete時)，可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。

　　3.預存程序可以重複使用,可減少資料庫開發人員的工作量

　　4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權

　　預存程序的種類：

　　1.系統預存程序：以sp_開頭,用來進行系統的各項設定.取得資訊.相關管理工作,

　　如 sp_help就是取得指定對象的相關資訊

　　2.擴充預存程序 以xp_開頭,用來叫用作業系統提供的功能

　　以下為引用的內容：

　　exec master..xp_cmdshell 'ping 10.8.16.1'

　　3.使用者自訂的預存程序,這是我們所指的預存程序

　　常用格式

　　以下為引用的內容：

　　create procedure procedue_name

　　[@parameter data_type][output]

　　[with]{recompile|encryption}

　　as

　　sql_statement

解釋:

　　output：表示此參數是可傳回的

　　with {recompile|encryption}

　　recompile:表示每次執行此預存程序時都重新編譯一次

　　encryption:所建立的預存程序的內容會被加密

　　二、sql預存程序學習：預存程序的建立

　　表book的內容如下

　　編號　　　　　書名　　　　　　價格

　　001 　　　c語言入門　　　　　$30

　　002 　powerbuilder報表開發  $52

　　執行個體1:查詢表book的內容的預存程序

　　create proc query_book
　　as
　　select * from book
　　go
　　exec query_book

　　執行個體2:加入一筆記錄到表book,並查詢此表中所有書籍的總金額

　　create proc insert_book
　　@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output
　　with encryption ---------加密
　　as
　　insert book(編號,書名，價格) values(@param1,@param2,@param3)
　　select @param4=sum(價格) from book
　　go
執行例子:

　　declare @total_price money
　　exec insert_book '003','delphi 控制項開發指南',$100,@total_price
　　print '總金額為'+convert(varchar,@total_price)
　　go

　　預存程序的3種傳回值:

　　1.以return傳回整數

　　2.以output格式傳回參數

　　3.recordset

　　傳回值的區別:

　　output和return都可在批次程式中用變數接收,而recordset則傳回到執行批次的用戶端中

下面看一應用執行個體

預存程序如下：

create proc [dbo].[exchange_userfinance]
@uid int,
@utype int,
@uamount decimal(9,2),
@uafterremainamount decimal(9,2),
@uopid int,
@utext varchar(200),
@unote1 varchar(200),
@unote2 varchar(200),
@unote3 varchar(200),
@uremainamount decimal(9,2) output

 as
 declare @utime datetime
 set @utime=getdate()
 set @uremainamount=0.00
 begin
 begin tran
 insert into [ip_userfinance](
 [uid],[utime],[utype],[uamount],[uafterremainamount],[uopid],[utext],[unote1],[unote2],[unote3]
 )values(@uid,@utime,@utype,@uamount,@uafterremainamount,@uopid,@utext,@unote1,@unote2,@unote3)
 if @@rowcount<>1
  begin
   rollback tran
   set @uremainamount=0
   return
  end
 update [ip_userremainamount] set  [uremainamount] = @uafterremainamount,[utime] = @utime where uid=@uid
 if @@rowcount<>1
  begin
   rollback tran
   set @uremainamount=0
   return
  end
 commit tran
 set @uremainamount=(select uremainamount from ip_userremainamount where uid=@uid)
 end

.net調用

 public static decimal modifyofficeoragentfinance(ip_userfinance agentmodel)
        {

            sqlparameter[] parms = new sqlparameter[]
                        {
                           new sqlparameter ("@uid",sqldbtype.int  ,4),
                        
                        };
            parms[0].value = agentmodel.uid;
            object remainagent = null;
            try
            {
                remainagent = sqlhelper.executescalar(sqlhelper.connectionstring, commandtype.storedprocedure, "wsselectip_userinfofinanceone", parms);
                if (remainagent == null)
                {
                    return -1;
                }

            }catch(exception)
            {
                return 0;
            }
            sqlparameter[] parameters = {
         new sqlparameter("@uid", sqldbtype.int,4),
                        new sqlparameter("@utype", sqldbtype.int),
         new sqlparameter("@uamount", sqldbtype.decimal,9),
         new sqlparameter("@uafterremainamount", sqldbtype.decimal,9),
         new sqlparameter("@uopid", sqldbtype.int,4),
                        new sqlparameter("@utext", sqldbtype.varchar,200),
         new sqlparameter("@unote1", sqldbtype.varchar,200),
         new sqlparameter("@unote2", sqldbtype.varchar,200),
                        new sqlparameter("@unote3", sqldbtype.varchar,200),
                        new sqlparameter("@uremainamount", sqldbtype.decimal)};

            if (convert.todecimal(remainagent) < agentmodel.uamount)
            {
                return -2;
            }

            parameters[0].value = agentmodel.uid;
            parameters[1].value = agentmodel.utype;
            parameters[2].value = agentmodel.uamount;
            parameters[3].value = decimal.add(convert.todecimal(remainagent), agentmodel.uamount);
            parameters[4].value = agentmodel.uopid;
            parameters[5].value = agentmodel.utext;
            parameters[6].value = agentmodel.unote1;
            parameters[7].value = agentmodel.unote2;
            parameters[8].value = agentmodel.unote3;
            parameters[9].direction = parameterdirection.output;

            try
            {
                convert.todecimal(sqlhelper.executescalar(sqlhelper.connectionstring, commandtype.storedprocedure, "exchange_userfinance", parameters));
                return convert.todecimal(parameters[9].value);

            }
            catch
            {
                return 0.00m;
            }

        }