TSQL script with variables

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Willo
Posts: 34
Joined: Thu 24 Aug 2006 18:29

TSQL script with variables

Post by Willo » Mon 27 Feb 2012 22:19

Hi;

i inherit a sql script that declares two date variables to work, how can i pass those variables to the script from Delphi?

Tried parambyname but doesnt work...


This is part of the script...

Code: Select all


DECLARE
            @fecha_ini datetime,
            @fecha_fin datetime

SELECT  @fecha_ini = '2012-02-13', @fecha_fin = '2012-02-13'


DM.Query2.ParamByName('fecha_ini').AsDateTime  := Efecha1.DateTime;
DM.Query2.ParamByName('fecha_fin').AsDateTime  := Efecha2.DateTime; 



SELECT DISTINCT 
/*1*/	'Codigo de Institucion' = 'STER',
/*2*/	'Codigo de Transaccion' = RTRIM (op.nm_operacion) + '-' + RTRIM (il.nm_liquidacion),
/*3*/	'Fecha de Transaccion' = CONVERT(VARCHAR(10), op.fecha_operacion, 101),
/*4*/	'Fecha valor de Transaccion' = CONVERT(VARCHAR(10), op.fecha_valor, 101),
/*5*/	'Código de Moneda' = CASE op.nm_divisa_operada


AndreyZ

Post by AndreyZ » Tue 28 Feb 2012 11:34

Hello,

You are not using parameters, that's why the ParamByName method doesn't work. To create parameters, you should use colons. Here is an example:

Code: Select all

MSQuery.SQL.Text := 'select :par1 date1, :par2 date2';
MSQuery.ParamByName('par1').AsDate := StrToDate('2012-02-13');
MSQuery.ParamByName('par2').AsDate := StrToDate('2012-02-13');
MSQuery.Open;

Willo
Posts: 34
Joined: Thu 24 Aug 2006 18:29

Re: TSQL script with variables

Post by Willo » Tue 28 Feb 2012 21:45

Willo wrote:Hi;

i inherit a sql script that declares two date variables to work, how can i pass those variables to the script from Delphi?

Tried parambyname but doesnt work...


This is part of the script...

Code: Select all


DECLARE
            @fecha_ini datetime,
            @fecha_fin datetime

SELECT  @fecha_ini = '2012-02-13', @fecha_fin = '2012-02-13'


SELECT DISTINCT 
/*1*/	'Codigo de Institucion' = 'STER',
/*2*/	'Codigo de Transaccion' = RTRIM (op.nm_operacion) + '-' + RTRIM (il.nm_liquidacion),
/*3*/	'Fecha de Transaccion' = CONVERT(VARCHAR(10), op.fecha_operacion, 101),
/*4*/	'Fecha valor de Transaccion' = CONVERT(VARCHAR(10), op.fecha_valor, 101),
/*5*/	'Código de Moneda' = CASE op.nm_divisa_operada


AndreyZ

Post by AndreyZ » Wed 29 Feb 2012 13:05

DECLARE defines a SQL Server variable, but not a parameter. If you are using SQL Server variables, you can set their values only in the script. To work with parameters, you should use my above code (to create parameters, you should use colons). In case of using parameters, you don't need to use DECLARE.

Post Reply