stored procedure造成的Rowlock問題 |
尚未結案
|
sionsion
一般會員 發表:1 回覆:0 積分:0 註冊:2009-01-16 發送簡訊給我 |
請教?下各位大大我用 DELPHI 5的TStoredProc元件去call MS SqlServer中Law_AddList這支stored procedure 可是再開另外?支程式要查看此table卻發現了rowlock的問題 會造成程式的timeout請問有何解決方法 [code delphi] G_CASE_NO:=Editw1.Text; CO_NO := COPY(G_CASE_NO, 1, 2); LAW_ID := DBLookupComboBox1.KEYVALUE; LAW_NAME := DBLookupComboBox1.Text; LAW_ITEMID := DBLookupComboBox2.KEYVALUE; LAW_ITEMNAME := DBLookupComboBox2.Text; MEMO := MEMO1.Text; StoredProc1.ParamByName(' G_CaseNO').asstring:=G_CASE_NO; StoredProc1.ParamByName(' G_LawID').asstring:=LAW_ID; StoredProc1.ParamByName(' G_LawItemID').asstring:=LAW_ITEMID; StoredProc1.ParamByName(' G_Memo').asstring:=MEMO; StoredProc1.ParamByName(' G_DATE').asstring:=GETDATETIME1; StoredProc1.ParamByName(' G_UserID').asstring:=USERID; StoredProc1.ParamByName(' G_Court').asstring:=DBLookupComboBox4.text; StoredProc1.ParamByName(' G_CourtD').asstring:=DBLookupComboBox5.text; StoredProc1.ParamByName(' G_CYear').asstring:=ComboBox2.Text; StoredProc1.ParamByName(' G_Letter').asstring:=ComboBox1.Text; StoredProc1.ParamByName(' G_Number').asstring:=ComboBox4.text; StoredProc1.ParamByName(' G_Stock').asstring:=ComboBox3.text; StoredProc1.ParamByName(' G_Company').asstring:=ComboBox5.Text; StoredProc1.ParamByName(' G_Address').asstring:=ComboBox7.Text; StoredProc1.ParamByName(' G_Explain').asstring:=Combobox6.Text; StoredProc1.ParamByName(' G_Money').asstring:=EditW2.Text; StoredProc1.ParamByName(' G_Modify').asstring:=ComboBox8.Text; StoredProc1.ParamByName(' G_MissveNo').asstring:=EditW3.Text; StoredProc1.ParamByName(' VisAVis').asstring:=ComboBox9.Text; StoredProc1.ParamByName(' Action').asstring:='ADD'; StoredProc1.Prepare; StoredProc1.ExecProc; StoredProc1.GetResults; showmessage(StoredProc1.ParamByName('Result').asstring); ToolbarButton972Click(Sender); [/code] [code sql] /* Alter Date: 2008/09/25 Author: Walker Purpose: 新增案件法務動? */ CREATE PROCEDURE [dbo].[Law_AddList] Action varchar(10), G_CaseNO Varchar(15), G_LawID Varchar(6), G_LawItemID Varchar(6), G_Memo Varchar(200), G_DATE Varchar(20), G_UserID Varchar(6), G_Court varchar(200), G_CourtD varchar(200), G_CYear varchar(200), G_Letter varchar(200), G_Number varchar(200), G_Stock varchar(200), G_Company varchar(200), G_Address varchar(200), G_Explain varchar(200), G_SignDate varchar(20), G_SendDate varchar(20), G_ApplyDate varchar(20), G_Money float, G_DebtSum float, G_Modify varchar(50), G_MissveNo varchar(6), VisAVis varchar(25) AS -- G_Rate float, G_RateDate varchar(25), G_Cost float AS DECLARE --變數宣告區段 p_cursor cursor, Name varchar(20), ItemName varchar(20), CoNo varchar(2), MSG varchar(200), P_DETAIL VARCHAR(200), G_Rate float, G_RateDate varchar(25), G_Cost float begin begin transaction IF ACTION='ADD' BEGIN--0 --利用lawid 抓出名稱 SELECT Name=LAW_NAME FROM LAWACTION WHERE LAW_ID= G_LawID SELECT ItemName=LAW_ITEMNAME FROM LAWITEM WHERE LAW_ITEMID= G_LawItemID SET CoNo=SUBSTRING( G_CaseNO,1,2) IF SUBSTRING( G_LawItemID,3,2)='00' --如果是系統丟進來的新增00 BEGIN --刪掉, 避免重複接辦產生重覆資料 DELETE FROM LAWLIST WHERE CASE_NO= G_CaseNO AND Law_ID= G_LawID AND Law_ItemID= G_LawItemID AND C_DATE= G_DATE --捉取保人有無責 DBO.GETCASE_SONNO_SONTIME(CASE_NO,''E5-6'',''F'') AS 保人有無責 SELECT P_DETAIL= DBO.GETCASE_SONNO_SONTIME( G_CaseNO,'E5-6','F') -- 加入 捉取值 DEB037 , E7-C EXETIME= G_SignDate(或直捉取E7-A最大值), 利率 :PARM_I2 ; 起息日=parm6 SELECT G_SignDate=EXETIME, G_Rate =ISNULL(PARM_I2,0), G_RateDate =parm6 FROM DEB037 WHERE CASE_NO= G_CaseNO AND SON_NO='E7-A' --加入費用 G_Cost, 利用 G_CaseNO G_DATE DEB008中找費用 SELECT G_Cost =SUM(CHG_MONEY) FROM DEB008 WHERE CASE_NO= G_CaseNO AND CHG_COMP LIKE G_DATE '%' IF G_Cost>0 SET G_Cost= G_Cost-30 END --INSERT INTO DATA INSERT INTO LAWLIST VALUES( G_CaseNO, CoNo, G_LawID, Name, G_LawItemID, ItemName, P_DETAIL, G_Memo, G_DATE, G_UserID, G_Court, G_CourtD, G_CYear, G_Letter, G_Number, G_Stock, G_Company, G_Address, G_Explain, G_SignDate, G_SendDate, G_ApplyDate,0, G_Money, G_Rate, G_RateDate, G_Cost, G_DebtSum, G_Modify, G_MissveNo, VisAVis) --新增後發出訊息給主管 IF SUBSTRING( G_LawItemID,3,2)<>'00' --如果是系統丟進來的新增00 BEGIN select MSG=law_itemname ' 備註:' isnull(memo,'') ' 說明:' isnull(explain,'') ' 金額:' cast(isnull(money,0) as varchar) ' ' isnull(court,' ') isnull(cyear '年',' ') isnull(letter,' ') isnull(number '號',' ') isnull(stock '股',' ') from lawlist where CASE_NO= G_CASENO AND C_DATE= G_DATE AND LAW_ITEMID= G_LAWITEMID --exec dbo.auto_send_msg G_CaseNo, G_Date,'法務申請單', MSG END -----沒加入報表 exec dbo.Law_Report G_CaseNo, G_LawID, G_Date END--0 IF Action='EDIT' BEGIN --1 PRINT '1' END--1 --註銷此筆法務動? IF Action='DEL' BEGIN --2 UPDATE LAWLIST SET DELFLAG=1 WHERE CASE_NO= G_CaseNO AND LAW_ITEMID= G_LawItemID AND C_DATE= G_DATE END--2 if error<>0 goto problemcommit transaction return 0 problem: rollback transaction return 1 end GO [/code] 編輯記錄
sionsion 重新編輯於 2009-01-16 15:03:43, 註解 無‧
|
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |