delphi call sql server procedure message

最新推荐文章于 2020-11-25 11:48:25 发布

lao_yunger

最新推荐文章于 2020-11-25 11:48:25 发布

阅读量193

点赞数

分类专栏： delphi人生文章标签： sql 数据库

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

版权

delphi人生专栏收录该内容

33 篇文章 1 订阅

订阅专栏

unit Unit1;

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, Data.DB, FireDAC.Comp.DataSet,
FireDAC.Comp.Client;

type
TForm1 = class(TForm)
AbcConnection: TFDConnection;
PaProc: TFDStoredProc;
Memo1: TMemo;
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}
{SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T1](
   [P] [nchar](10) NOT NULL,
   [C1] [nchar](10) NOT NULL,
   [C2] [nchar](10) NOT NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
   [P] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO}

{-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 杨宗会
-- Create date: 2020-7-27
-- Description:验证成功和失败时的返回消息
-- =============================================

alter PROCEDURE PA
   @P varchar(20)
   ,@c1 varchar(20)
   ,@c2 varchar(20)
   ,@msg varchar(2000)='' output
AS
BEGIN
SET NOCOUNT ON;
   begin try
   INSERT INTO dbo.T1 VALUES ( @P, @c1,@c2);
   set @msg = 'New Record Inserted';

   end try
   begin catch ---CAST('abc' AS varchar(5))
       set @msg = '错误级别：'+CAST( ERROR_SEVERITY() AS varchar(10)) +': '+CHAR(13)
       set @msg =@msg+' '+ '错误状态：'+CAST(ERROR_STATE() AS varchar(10)) +': ' +CHAR(13)
       set @msg= @msg+' '+ '错误号：'+CAST( ERROR_NUMBER() AS varchar(10)) +': ' +CHAR(13)
       set @msg =@msg+' '+ '错误行号：' +CAST( ERROR_LINE ()AS varchar(10))   +': ' +CHAR(13)
       set @msg =@msg+' '+ '错误消息：'+CAST(ERROR_MESSAGE() AS varchar(100)) +': ' +CHAR(13)
       raiserror(@msg,16,1)
   end catch
--SET NOCOUNT Off;
END
GO
---==================================================================}

procedure TForm1.FormCreate(Sender: TObject);
begin
try
memo1.Lines.Clear;
PaProc.ExecProc('abc.dbo.PA',['p1', 'C1','c2']);
memo1.Lines.Add(PaProc.ParamByName('@msg').Value)
except
on E:exception do
begin
memo1.Lines.Add(PaProc.ParamByName('@msg').Value)
end;

end;
end;

end.

object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 201
ClientWidth = 447
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object Memo1: TMemo
AlignWithMargins = True
Left = 3
Top = 109
Width = 441
Height = 89
Align = alBottom
Lines.Strings = (
'Memo1')
TabOrder = 0
WantTabs = True
end
object AbcConnection: TFDConnection
Params.Strings = (
'ConnectionDef=ABC')
Connected = True
LoginPrompt = False
Left = 225
Top = 55
end
object PaProc: TFDStoredProc
Connection = AbcConnection
StoredProcName = 'abc.dbo.PA'
Left = 225
Top = 103
ParamData = <
item
Position = 1
Name = '@RETURN_VALUE'
DataType = ftInteger
ParamType = ptResult
end
item
Position = 2
Name = '@P'
DataType = ftString
ParamType = ptInput
Size = 20
end
item
Position = 3
Name = '@c1'
DataType = ftString
ParamType = ptInput
Size = 20
end
item
Position = 4
Name = '@c2'
DataType = ftString
ParamType = ptInput
Size = 20
end
item
Position = 5
Name = '@msg'
DataType = ftString
ParamType = ptOutput
Size = 2000
Value = 0
end>
end
end