SQL stored procedure call fails with string parameters with commas in them

[TylerMitton]

I'm passing a string parameter to a stored procedure and the commas in my strings are causing problems. Any string with a comma in it causes the call to the stored procedure to fail... I assume it's thinking that the next variable starts as soon as it sees a comma even if it is contained in a single string.

How can pass a parameter with a comma in it to a stored procedure?

Thanks!

Tyler

[TylerMitton]

I am using variables to hold my strings and it isn't working...

[forumsupport]

Hello,

To get around this, we can replace every occurrence of comma with space and then pass the modified string to stored procedure. We can use VB Script to replace the comma.

We have attached a sample task for your reference. To run this task,

. Please copy .atmn under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.
. Copy .mdb under C:\stored_procedure folder.
. Copy .vbs under C:\.

Hope that helps.

[TylerMitton]

Thanks for the reply. I may not have been clear in my initial message. I wasn't looking for a way to remove commas from my strings. I was asking for a way to get strings with commas into my stored procedure... I need the commas.

One way I can think of would be to replace all commas with a unique string, like "[;]", then within the procedure you could convert all instances of those back to commas, but that is a really hacky workaround and is prone to errors.

Is there a reason commas aren't allowed?

Can you think of a way to get commas into the stored procedures?

[trentonponder]

Just put "" around the field. If the field you're trying to write to is a text field then the DB (depending on which one) will usually interpret the comma as the start of a new field, if its numerical usually it depends on the DB type. To be safe, place "" around every value you want to pass. It works for me with DBs such as MS Access where I can run a procedure directly within Access no problem, but from AA it requires quotes.

[TylerMitton]

Thanks Trent!

I think I've tried this already but I'll double check and report back.

I am using SQL Server 2008.

Tyler

[TylerMitton]

Ya, I just tried that again and it doesn't work with quotes around the string. I made a test task and I tried doing the following tests, non of them worked:

-- TEST 1 (how i think it should work)--
usp_mySprocName('$Prompt-Assignment$')

where $Prompt-Assignment$ =

My string, with comma.

-- TEST 2 --
usp_mySprocName("$Prompt-Assignment$")

where $Prompt-Assignment$ =

My string, with comma.

-- TEST 3 --
usp_mySprocName($Prompt-Assignment$)

where $Prompt-Assignment$ =

"My string, with comma."

-- TEST 4 --
usp_mySprocName($Prompt-Assignment$)

where $Prompt-Assignment$ =

'My string, with comma.'

[trentonponder]

Try running SQL directly from AA and compare results.

[forumsupport]

Hello,

You can try this,

. Instead of comma (,) use colon (: ) as a part of parameter string.
. Insert Replace function in stored procedure to convert every colon of parameter string into comma.

We have attached a sample task that works with Oracle successfully.

Please copy .atmn under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.

Query.txt contains the syntax to create stored procedure.

Hope that helps.

[TylerMitton]

Ya, that's what I said in my third post of this thread...