1.表结构如下(预算数据明细表):
CREATE TABLE [dbo].[BA_FeeDetail]( [ID] [int] IDENTITY(1,1) NOT NULL, [FeeDeptID] [nvarchar](4) NULL, [FeeDate] [int] NULL, [FeeCode] [nvarchar](10) NULL, [FeeType] [nvarchar](1) NULL, [BAType] [nvarchar](1) NULL, [FeeAmt] [float] NULL ) ON [PRIMARY]
2.创建存储过程(用于取得某部门某次预算的某月某费目的预算额)
CREATE PROCEDURE [dbo].[GetBudgetAmt] -- Add the parameters for the stored procedure here @DeptID nvarchar(4) = '7120', @FeeDate int = 201301, @FeeCode nvarchar(8) ='31301' , @FeeType nvarchar(1) = '2', @BAType nvarchar(1)='1', @FeeAmt float output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @FeeAmt = FeeAmt from BA_FeeDetail where ( FeeCode = @FeeCode and FeeType = @FeeType and FeeDate = @FeeDate and FeeDeptID = @DeptID and BAType = @BAType ) Return @FeeAmt END
3.delphi 程序中调用(使用ADOConnect 和 ADOStoreProc )
procedure TForm1.btn1Click(Sender: TObject); Var BudgetAmt:Single; begin with ADOSP1 do begin ProcedureName :='GetBudgetAmt'; Parameters.Refresh; //必须有本语句,刷新参数 Parameters.ParamByName('@DeptID').Value:=EdtDept.Text; Parameters.ParamByName('@FeeDate').Value:=EdtFeeDate.Text; Parameters.ParamByName('@FeeCode').Value:=EdtFeeCode.Text; Parameters.ParamByName('@FeeType').Value:=EdtFeeType.Text; Parameters.ParamByName('@BAType').Value:=EdtBAType.Text; Parameters.ParamByName('@FeeAmt').Value:=0.00; ExecProc; BudgetAmt:=Parameters.ParamByName('@FeeAmt').Value; edtFeeAmt.Text:=Format('%.2f',[BudgetAmt]); end; end;