delphi 调用带有返回值的sql SERver 2008 存储过程 |
您所在的位置:网站首页 › delphi过程 › delphi 调用带有返回值的sql SERver 2008 存储过程 |
存储过程内容如下: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Author,,Name -- Create date: Create Date,, -- Description: Description,, -- ============================================= CREATE PROCEDURE base.sptEmployee -- Add the parameters for the stored procedure here /*@Param1, sysname, @p1 Datatype_For_Param1, , int = Default_Value_For_Param1, , 0, @Param2, sysname, @p2 Datatype_For_Param2, , int = Default_Value_For_Param2, , 0*/ @fCode nvarchar(50) ,@fName nvarchar(50) ,@fDeptID int ,@fDeptCode nvarchar(50) ,@fTypeID int ,@fSex nvarchar(50) ,@fPosition nvarchar(50) ,@fAddress nvarchar(50) ,@fEmail nvarchar(100) ,@fTel nvarchar(50) ,@fMobile nvarchar(50) ,@fBP nvarchar(50) ,@fDegree nvarchar(50) ,@fCollege nvarchar(50) ,@fBirthAddress nvarchar(50) ,@fBirthday datetime ,@fInTime datetime ,@fOutTime datetime ,@fMoney numeric(10,2) ,@fStatus bit ,@fNotes nvarchar(200) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @BUSINESS_ENTITY_ID_NOT_FOUND INT=-1000; DECLARE @DUPLICATE_RATE_CHANGE INT =-2000; Declare @error int; Begin try begin transaction insert into base.tEmployee values( @fCode ,@fName ,@fDeptID ,@fDeptCode ,@fTypeID ,@fSex ,@fPosition ,@fAddress ,@fEmail ,@fTel ,@fMobile ,@fBP ,@fDegree ,@fCollege ,@fBirthAddress ,@fBirthday ,@fInTime ,@fOutTime ,@fMoney ,@fStatus ,@fNotes ) set @error=@@ERROR; if @@ROWCOUNT>0 print '成功插入一行' commit transaction End try Begin catch print @@error if @@TRANCOUNT>0 begin rollback transaction end if ERROR_NUMBER()=2627 begin print '插入重复'; RETURN @DUPLICATE_RATE_CHANGE; end else Return @error ; End catch END /*SP结束*/ GO ; delphi xe 10.3 object Form1: TForm1 Left = 0 Top = 0 Caption = 'Form1' ClientHeight = 482 ClientWidth = 702 Color = clBtnFace Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = -11 Font.Name = 'Tahoma' Font.Style = [] OldCreateOrder = False PixelsPerInch = 96 TextHeight = 13 object Label1: TLabel Left = 344 Top = 256 Width = 31 Height = 13 Caption = 'Label1' end object Button1: TButton Left = 344 Top = 225 Width = 75 Height = 25 Caption = 'Button1' TabOrder = 0 OnClick = Button1Click end object ClothuConnection: TFDConnection Params.Strings = ( 'ConnectionDef=ClothU') Connected = True LoginPrompt = False Left = 173 Top = 110 end object DataSource1: TDataSource Left = 48 Top = 240 end object FDStoredProc1: TFDStoredProc Connection = ClothuConnection SchemaName = 'base' StoredProcName = 'ClothU..sptEmployee' Left = 160 Top = 224 ParamData = end end unit storeprocedure; interface uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.MSSQL, FireDAC.Phys.MSSQLDef, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Vcl.StdCtrls, FireDAC.Comp.Client, Data.DB, FireDAC.Comp.DataSet; type TForm1 = class(TForm) ClothuConnection: TFDConnection; DataSource1: TDataSource; FDStoredProc1: TFDStoredProc; Button1: TButton; Label1: TLabel; procedure Button1Click(Sender: TObject); private { Private declarations } public { Public declarations } end; var Form1: TForm1; implementation {$R *.dfm} procedure TForm1.Button1Click(Sender: TObject); begin FDStoredProc1.Connection:=ClothuConnection; FDStoredProc1.SchemaName:= 'base'; FDStoredProc1.StoredProcName := 'sptEmployee';//以上三 行,其实在设计时已设计好 FDStoredProc1.Prepare; FDStoredProc1.ParamByName('@fCode').Value:= 1008666 ; FDStoredProc1.ParamByName('@fName').Value:= '十三姨' ; FDStoredProc1.ParamByName('@fDeptID').Value:= 43 ; FDStoredProc1.ParamByName('@fDeptCode').Value:= '8866' ; FDStoredProc1.ParamByName('@fTypeID').Value:=26 ; FDStoredProc1.ParamByName('@fSex').Value:= '女' ; FDStoredProc1.ParamByName('@fPosition').Value:= '' ; FDStoredProc1.ParamByName('@fAddress').Value:= '' ; FDStoredProc1.ParamByName('@fEmail').Value:= '' ; FDStoredProc1.ParamByName('@fTel').Value:= '' ; FDStoredProc1.ParamByName('@fMobile').Value:= '' ; FDStoredProc1.ParamByName('@fBP').Value:= '' ; FDStoredProc1.ParamByName('@fDegree').Value:= '' ; FDStoredProc1.ParamByName('@fCollege').Value:= '' ; FDStoredProc1.ParamByName('@fBirthAddress').Value:= '' ; FDStoredProc1.ParamByName('@fBirthday').Value:=strtoDate('1968/1/1') ; //FormatdateTime('ddddd',now); FDStoredProc1.ParamByName('@fInTime').Value:=strtoDate('2012/12/12') ; FDStoredProc1.ParamByName('@fOutTime').Value:=strtoDate('2016/01/02') ; FDStoredProc1.ParamByName('@fMoney').Value:= 0.0 ; FDStoredProc1.ParamByName('@fStatus').Value:= 1 ; FDStoredProc1.ParamByName('@fNotes').Value:= '' ; FDStoredProc1.ExecProc; label1.Caption:=VarToStr( FDStoredProc1.Params.ParamByName('@RETURN_VALUE').Value) // end; end. 运行结果:
以上仅当笔记本,有不对的地方不要喷 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |