Take a look at this simple example (Microsoft SQL Server):
- an SQL Table with two entries:
Code: Select all
CREATE TABLE TESTNUM(NUMFIELD NUMERIC(22,10));
INSERT INTO TESTNUM VALUES(0.0000000001);
INSERT INTO TESTNUM VALUES(0.0000000002);
Code: Select all
SELECT * FROM TESTNUM
WHERE (NUMFIELD = :NF) OR (:NF = 1)
(select rows with the specified parameter value
or all rows when the parameter equals the constant value.)
Code: Select all
Options.NumericType := TDANumericType.ntFmtBCD;
Before opening the query (q: TMSQuery), we are defining the following parameter:
(which exists twice in Params object)
Code: Select all
q.Options.DescribeParams := False;
q.Params[0].DataType := ftFMTBcd;
q.Params[0].Precision := 22;
q.Params[0].NumericScale := 10;
q.Params[1].DataType := ftFMTBcd;
q.Params[1].Precision := 22;
q.Params[1].NumericScale := 10;
Code: Select all
q.Prepared;
Code: Select all
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 money OUTPUT,@P2 money OUTPUT',N'SELECT * FROM TESTNUM
WHERE (NUMFIELD = @P1)
OR (@P2 = 1)',1
select @p1
After setting the parameter values, we open the query with the following example:
Code: Select all
q.Params[0].AsFMTBCD := '0,0000000001';
q.Params[1].AsFMTBCD := '0,0000000001';
q.Open;
Code: Select all
exec sp_execute 1,$0.0000,$0.0000
The question is:
How can we treat the data type of the parameter as ftFMTBcd (NUMERIC) with the defined precision and scale?
SDAC V. 7.3.16, Delphi 10.1 Berlin
Thx, Henrik