Database Execute Query using parameters and parameter value has a space in it

[soneill437]

How do you deal with a situation where you would like to use Parameters in the Execute Query activity and your parameter value has a space in it which bring in quotes ? Here is a sample query (I have the lined broken out for readability):

“SELECT
mr.rolename
FROM
main_users mu
INNER JOIN main_roles mr
ON mu.emprole = mr.id AND mu.userfullname = ‘John Jones’;”

As seen above, the mu.userfullname value has a space in it so quotes are needed. Using a parameter called “FullName” which accepts that value as part of the Execute Query definition, I would think it would look something like this:

“SELECT
mr.rolename
FROM
main_users mu
INNER JOIN main_roles mr
ON mu.emprole = mr.id AND mu.userfullname = ‘@FullName’;”

However, this doesn’t work as I hoped. I get back nothing from the query. I think the single quotes are messing up the parameter interpolation or something.

In the end, I just end up using string concatenation in an immediately prior Assign activity to craft the SQL (as a single line of text) with the appropriate quotes and name value and it works fine.

Just curious if anyone has hit this with Execute Query and how they dealt with it using parameter value that have spaces in them. Assuming you can that is.

[ClaytonM]

I have never been able to get parameters to work, but it’s been a while and I don’t know if the activity package has been updated or not.

I basically did what you said, I concatenated the variable/argument into the sql query string.

One alternative to concatenating it, which I think was what I did last time actually, is to use String.Format().

So like if you have your Select string like this:

“SELECT
mr.rolename
FROM
main_users mu
INNER JOIN main_roles mr
ON mu.emprole = mr.id AND mu.userfullname = ‘{0}’;”

Then, just input the string into the Execute query like this:

String.Format(queryString, in_FullName)

where in_FullName is an argument in the workflow (not the execute query activity), and the parameters would not be used in this case.

Regards.

Let us know if you find a way to use the parameters though.

[ClaytonM]

and you can have as many variables/arguments placed into the string by using {0},{1},…,{x}

[soneill437]

Works great !! Thanks for the suggestion !