Passing String as Parameter in SQL Query

[jpreziuso]

This may be confusing but I am hoping someone can help. So I am trying to pass a string variable into an SQL query but I believe the quotes are messing it up. For example:

When I don’t use a parameter and I just pass in the values (‘dog’),(‘cat’) it works fine. But when I build that same string into a variable to use as a parameter (AnimalString), it adds double quotes around the string that seem to be causing the issue.

Can anyone help with this? Thanks

[sarathi125 (Parthasarathi)]

@jpreziuso,

Set Parameters (as strings) in the relevant collection argument. Make sure that names match of course.

[jpreziuso]

I do have the parameter set as a string

[bcorrea (Bruno Correa)]

what do you mean when i pass the valueS, is it a string or not? cause if it is string then you have to pass one value…

[jpreziuso]

I am trying to pass in values to a SQL column. So when I pass them in individually like (‘dog’),(‘cat’) it works fine. But when I build a string “(‘dog’),(‘cat’)” and store it as the parameter AnimalString to be passed into the query…the double quotes (from it being a String datatype instead of just text) cause an issue

[bcorrea (Bruno Correa)]

this string looks wrong… what are those ( and )… also those apostrophes should be escaped with one more in each…

[jpreziuso]

The parentheses and apostrophes are required since this is being used in a SQL query. That is the SQL syntax

The string I build to pass in as a parameter prints out exactly how I want it to look. The issue comes because it is being passed in as a string, so double quotes are placed around it which causes an issue

[bcorrea (Bruno Correa)]

@jpreziuso

“(‘dog’),(‘cat’)”

just send like this: "(''dog''),(''ca'')"

[jpreziuso]

That does not seem to help. When I give include (‘dog’),(‘cat’) in the SQL query, it works fine. But when I attempt to assign that same string to a variable and pass it in as a parameter, it causes an issue. Does that help to explain?

[bcorrea (Bruno Correa)]

not really… do you have a string column in your database and you want to have values like (‘dog’),(‘cat’) right? if you have a sql procedure to insert them and one of your variables should take a string with that, then what is the result inside that column?

[jpreziuso]

Yes I want to add those values to the column of a temp table and then complete a join with that temp table and another table. The result of the join is skewed because of the quotes. I know it is confusing though. Thank you for your help

[Mateus_Oliveira]

did u solve?same problem here

[KuRcZaK (Wojciech Kucia)]

You can either concatenate the string with the parameters, but this approach may open you for SQL injection vulnerability.

A better way is to leverage SQL query parameters.
If you use ODBC connection simply replace each parameter in the SQL query with “?”
e.g.
SELECT * FROM tblName WHERE arg1=? and arg2=?
(for SQL Server driver use SELECT * FROM tblName WHERE arg1=@param1_name and arg2=@param2_name

Then create two parameters (Name does not matter for ODBC connection) ordered as in the query.

[jpreziuso]

I never solved m issue exactly. I ended up passing each item in individually, running the join, and then concatenating the results of all the individual joins together

[Nishant_Shrivastava1 (Nishant Shrivastava)]

Thanks a lot. This worked for me as a solution with ODBC connection. I am surprised to know that depending on your connection type the query language needs to be adapted. May be I am too ignorant and so I am amused. Anyway, thanks a ton!