存储过程内容如下：
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 = <
item
Position = 1
Name = '@RETURN_VALUE'
DataType = ftInteger
ParamType = ptResult
Value = 0
end
item
Position = 2
Name = '@fCode'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 3
Name = '@fName'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 4
Name = '@fDeptID'
DataType = ftInteger
ParamType = ptInput
end
item
Position = 5
Name = '@fDeptCode'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 6
Name = '@fTypeID'
DataType = ftInteger
ParamType = ptInput
end
item
Position = 7
Name = '@fSex'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 8
Name = '@fPosition'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 9
Name = '@fAddress'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 10
Name = '@fEmail'
DataType = ftWideString
ParamType = ptInput
Size = 100
end
item
Position = 11
Name = '@fTel'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 12
Name = '@fMobile'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 13
Name = '@fBP'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 14
Name = '@fDegree'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 15
Name = '@fCollege'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 16
Name = '@fBirthAddress'
DataType = ftWideString
ParamType = ptInput
Size = 50
end
item
Position = 17
Name = '@fBirthday'
DataType = ftDateTime
NumericScale = 3
ParamType = ptInput
end
item
Position = 18
Name = '@fInTime'
DataType = ftDateTime
NumericScale = 3
ParamType = ptInput
end
item
Position = 19
Name = '@fOutTime'
DataType = ftDateTime
NumericScale = 3
ParamType = ptInput
end
item
Position = 20
Name = '@fMoney'
DataType = ftBCD
Precision = 10
NumericScale = 2
ParamType = ptInput
end
item
Position = 21
Name = '@fStatus'
DataType = ftBoolean
ParamType = ptInput
end
item
Position = 22
Name = '@fNotes'
DataType = ftWideString
ParamType = ptInput
Size = 200
end>
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, p.Client, Data.DB,
p.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('/12/12') ;
FDStoredProc1.ParamByName('@fOutTime').Value:=strtoDate('/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.
运行结果：
以上仅当笔记本，有不对的地方不要喷
