Devart Forums

Posted: **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

Posted: **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;

Posted: **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

Posted: **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.

Devart Forums

TSQL script with variables

TSQL script with variables

Re: TSQL script with variables