請教?下各位大大我用
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]

編輯記錄