Example of using parameters for execute non-query

Hi all,
can anyone please provide a working example of an execute non-query activity which uses parameters ? that is,
I am trying to use @ParameterName in my query. However, the query is not being substitued for the values as defined in my parameter list. What am I doing wrong ?
my query is something like update table Set fieldname=@ParameterName where fieldname=@parameterx
I am sure that the parameter has the correct value (used Log Message to check that).
The SQL statement which is being sent to the db does NOT substitue the @parameterName with the value.
help is much appreciated !!

Thanks

1 Like

anyone?

Hi,

updateQuery = “update table set Approval=’”+approval_status.ToString.Trim+"’ where REFNO=’"+refNumber.ToString.Trim+"’"

Here ‘approval_status’ and ‘refNumber’ are the parameters (string variables in the workflow.)

Regards,
Shiju Mathew

Thanks for reply. That is what I eventually had to do.
However, the documentation mentions the usage of the feature of providing Parameters, in the Collection parameters attribute of the activity. It mentions the use of parameters in the format of @ParameterName which is automatically substituted at run time.
I am looking for working examples of that

1 Like

I was not aware at all about the Parameters. My bad. I can see some topics on it in forum. I will read through and will try using that in my work flows. Thanks.

1 Like

anyone?

hi all,
I wonder if anyone can tell me if this is a bug or the correct way of using parameters in a execute non-query activity? the documentation was clear and I followed it, the behavior is not as documented.
thank you!!

1 Like

Did you ever make any progress on this? I’m getting ready to start adding some database update activities and would be happy to hear your advise of using @parameter substitution or building dynamic SQL strings.

Hi,
It might depend on how u define your connection not sure.
For me it works like that (if I have 3 parameters)

insert into table values (?, ?, ?)

Cheers

In case of Oracle database, bind variable can use like below.

var a number;
exec :a := 1000;
select * from emp where empno > a;

In “where” sql statement of Execute Query Activity, i have tried to use a paramerter like below, but the paramerter was not replaced with its value from the dictionary.

In Parameters, created one argument like below.
Name: a
Direcrion: In
Type: Int32
Value: 1000

In Sql property, seted a select statement string like below.
"SELECT * FROM EMP WHERE EMPNO > @a "

If calling a SQL Server stored procedure, use the Execute Non Query activity, set the CommandType to StoredProcedure, then only include the stored procedure name (without parameter references) in the Sql property. You can then update the Parameters collection property with your parameter(s) - I am pretty sure that the order of the parameters is what matters in the collection. This is what worked for me.