delphi 调用带有返回值的sql SERver 2008 存储过程

lao_yunger

于 2020-01-03 19:20:33 发布

阅读量647

点赞数 1

分类专栏：数据库 delphi 技巧文章标签： sql server delphi 存储过程返回值

本文链接：https://blog.csdn.net/qq_25439957/article/details/103825528

版权

delphi 同时被 3 个专栏收录

31 篇文章 0 订阅

订阅专栏

数据库

26 篇文章 1 订阅

订阅专栏

技巧

10 篇文章 0 订阅

订阅专栏

存储过程内容如下：

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, 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;