Example of using parameters for execute non-query

Help
,
1

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

2

anyone?

3

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

4

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

5

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.

6

anyone?

7

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!!

8

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.

9

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

10

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 "

11

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.

12

If anyone still looking for an answer to this question, I managed to do it as per below steps:-

  1. Create a parameter and change the type to Object
    image

    image
    image1400×126 7.81 KB

  2. Within the SQL command, I am doing an insert statement. I passed the @in_EndDate which is the parameter I created earlier and wrap it within the open inverted comma - see below

    image
    image314×119 4.61 KB

  3. Results are per below in the SQL table

    image
    image144×218 1.41 KB

Note: The column I created within the table is DATETIME type.

Hope this helps.