SQL processing scheme Data Processing Scheme

SQL processing scheme Data Processing Scheme

 

There are two data processing solutions:

① Application mathematical processing solutions, which are commonly used in data processing, are mainly implemented using database controls and code.

② SQL data processing solution. Most of the work of data processing is on the SQL Server, using triggers, stored procedures, etc. Applications mainly send call parameters to the SQL server.

 

1. You can control the execution of stored procedures in Delphi.

① StoredProc components such as BDE, DBG, and DBExpress

② Query components. Similarly, they are in BDE, DBG, and DBExpress.

Query and StroProc can both be called with and without return values. It is easier to use StroProc than Query. It must be specially used to execute stored procedures, it automatically reads the parameter numbers required by the stored procedure,

 

If you want to execute a view with a returned dataset, you can only use the Query component. In fact, the view and table have the same usage. Select * From View File

 

How can I use the Query and StoredProc components to execute a stored procedure?

 

The following is an example of adding records using stored procedures.

The Stored Procedure Code is as follows:
--------------------------------------
Create procedure addmenu (@ id varchar (10), @ winename varchar (20)
BEGIN
Set NoCount On
Insert Into menu (Liquor number, liquor name) Values (@ ID, @ WINENAME)
Set NoCount Off
END
GO
--------------- This is the case when using Query (the parameter must be passed in dynamically )------------------------------------------------

The application uses ADOQuery to execute this stored procedure (do not set parameter names for ADOQuery-> Parameters)
Procedure TForm1.Button1Click (Sender: TObject );
Begin
ADOQuery1. SQL. Clear;
ADOQuery1. SQL. Add ('addmenu; 1: @ ID,: @ WineName '); // Add (; 1) to the name of the stored procedure, followed by the parameter to be passed in
ADOQuery1.Parameters. ParamByName ('@ id'). Value: = Edit1.Text; // assign values to parameters
ADOQuery1.Parameters. ParamByName ('@ WINENAME'). Value: = Edit2.Text;
ADOQuery1.ExecSQL;
End;
------------------ StroedProc is like this (dynamic or static )--------------------------------------------------
ADOStoredProc1.Parameters. ParamByName ('@ id'). Value: = Edit1.Text;
ADOStoredProc1.Parameters. ParamByName ('@ WINENAME'). Value: = Edit2.Text;
ADOStoredProc1.ExecProc;
----------------------------------------------------------------------------

 

 

Another example is provided.

The Stored Procedure Code is as follows (the value of A + B is returned ):
Create procedure HaHa (@ a int, @ B int, @ c int OutPut)
AS
Begin
Select @ c = @ a + @ B
End
GO
----------------- Dynamically set the StroedProc parameter and call the Stored Procedure Method -------------------------------------------------------------
Procedure TForm1.Button3Click (Sender: TObject );
Var
S: string;
Begin
// ----- Write 1st parameter ----------
ADOStoredProc1.ProcedureName: = 'haha; 1'; // change the name of the stored procedure
ADOStoredProc1.Parameters. Clear; // Clear parameters
ADOStoredProc1.Parameters. AddParameter; // call the Add parameter process.
ADOStoredProc1.Parameters [0]. Name: = '@ a'; // set the parameter Name.
ADOStoredProc1.Parameters [0]. DataType: = ftInteger; // parameter data type
ADOStoredProc1.Parameters [0]. Direction: = pdInput; // whether the output parameter is
// ----- Write 2nd parameter ----------
ADOStoredProc1.Parameters. AddParameter;
ADOStoredProc1.Parameters [1]. Name: = '@ B ';
ADOStoredProc1.Parameters [1]. DataType: = ftInteger;
ADOStoredProc1.Parameters [1]. Direction: = pdInput;
// ----- Write 3rd parameter ----------
ADOStoredProc1.Parameters. AddParameter;
ADOStoredProc1.Parameters [2]. Name: = '@ C ';
ADOStoredProc1.Parameters [2]. DataType: = ftInteger;
ADOStoredProc1.Parameters [2]. Direction: = pdInputOutput;
// ------ Assign the parameter value ---------------
ADOStoredProc1.Parameters. ParamByName ('@ A'). Value: = 12;
ADOStoredProc1.Parameters. ParamByName ('@ B'). Value: = 13;
ADOStoredProc1.ExecProc;
ShowMessage (VarToStr (ADOStoredProc1.Parameters. ParamByName ('@ C'). Value ));
End;
--------- A Query call can only dynamically set the parameter number for stored procedures with returned parameters. The method is as follows -----------------------------------
Procedure TForm1.Button2Click (Sender: TObject );
Var
C: Variant;
Begin
ADOQuery2. SQL. Clear;
ADOQuery2. SQL. add ('haha; 1: @ a,: @ B,: @ c output'); // note that when setting the parameter value dynamically, it indicates that it is an outbound parameter. The default value is an inbound parameter.
ADOQuery2.Parameters. ParamByName ('@ A'). Value: = 12;
ADOQuery2.Parameters. ParamByName ('@ B'). Value: = 13;
ADOQuery2.ExecSQL;
C: = ADOQuery2.Parameters. ParamByName ('@ C'). Value;
ShowMessage (VarToStr (c ));
End;