Use string of numbers as SqlParameter to SQL query resource with IN Operator

how to pass parameter in sql query
how to pass parameter in dynamic sql query
how to pass string variable in sql query
how to pass parameter in sql query in oracle
how to pass parameter in sql query in java
how to pass parameter in sql query c#
how to pass variable in sql query
sql parameter list of strings

I am trying to pass a string p_strIds with number values separated by ",":

2844099,2844100,2844101,2844102,2844103,2844104,2844105,2844106,2844107,2844108,2844109,2844110,2844111,2844112,2844113,2844114,2844115,2844116,2844117,2844118

The string is used as a SqlParameter:

mySqlCommand.Parameters.Add(new SqlParameter("@p_Ids", p_strValores));

to be used by the following resource (added as resource) query in the IN Operator:

UPDATE tbl_Datos 
SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') 
WHERE col_Id in (@p_Ids)

The query and the IN Operator should end up like this:

UPDATE tbl_Datos 
SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') 
WHERE col_Id in (2844099,2844100,2844101,2844102,2844103,2844104,2844105,2844106,2844107,2844108,2844109,2844110,2844111,2844112,2844113,2844114,2844115,2844116,2844117,2844118)

But it says it cannot convert nvarchar to int, how can I format parameter to be used at IN(...Ids...)

It works if I use the parameter p_strIds with string.Format(queryString, p_strIds) like this:

queryString = UPDATE tbl_Datos SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') WHERE col_Id in ({0})
strSql = string.Format(queryString, p_strValores)
mySqlCommand = new SqlCommand(strSql, m_obSqlConnection);

any ideas about how to do it in the first approach with the sql statement as a resource?

Thanks


The either col_Id column on tbl_Datos or col_Leyenda in table tbl_Leyenda is declared as a data type NVARCHAR. There is probably data in that column with at least some non-numeric characters in it.

When SQL tries to run your WHERE statment: WHERE col_Id in (@Ids)

It fails to convert the non-numeric data in col_Id to your list of data in @Ids that it is assuming are integers.

You can fix this by putting single quote marks around each Id in your list. It will look more like this:

'2844099','2844100','2844101','2844102','2844103','2844104','2844105','2844106','2844107','2844108','2844109','2844110','2844111','2844112','2844113','2844114','2844115','2844116','2844117','2844118'

It could also be that the variable @p_Leyenda is being passed in as an integer value. You should attempt to force that to be a string as well. Similarly to your list of col_Ids above.

How to Pass Parameters in Dynamic T-SQL Query – {coding}Sight, declare @sql varchar(100) = 'select 1+1' execute( @sql) All string values are potentially dangerous code. Lists of values in the IN clause To write parameterized dynamic queries it is better to use sp_executesql instead of executing. In this Use of this resource constitutes acceptance of Privacy Policy. Example: SQL IN Operator. To know whether the search value 15 is present within the specified range from the DUAL table, the following SQL statement can be used : SQL Code: SELECT 15 IN (5,10,15,20,56,69) FROM dual; Pictorial Presentation : SQL IN operator with text value. The checking value of IN operator can be a string or word or sentence.


What you need here is a split function.

create a split function as shown here

CREATE FUNCTION [dbo].[SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
            @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
        set @iStart = 2
        insert into @tParts
        values( null )
    end
    else 
        set @iStart = 1
    while 1=1
    begin
        set @iPos = charindex( @cDelimiter, @sString, @iStart )
        if @iPos = 0
            set @iPos = len( @sString )+1
        if @iPos - @iStart > 0          
            insert into @tParts
            values  ( substring( @sString, @iStart, @iPos-@iStart ))
        else
            insert into @tParts
            values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString ) 
            break
    end
    RETURN

END

and then you change your query like below:

UPDATE tbl_Datos 
SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') 
WHERE col_Id in (Select part from SplitString(@p_Leyenda,','))

Using Parameters for SQL Server Queries and Stored Procedures, Generally, when creating a condition in a query where you might use one of several values, it makes sense to parameterize. But, as will be  We can't do it, because SQL has no concept of Lists, or array or other useful data structures - it only knows about tables (and table based information) so it converts the string list into a table structure when it compiles the command - and it can't compile a variable string, so it complains and you get annoyed. Or at least, I do.


Finally , the only way is to convert string to List:

List<string> p_Ids= p_strValores.Split(',').ToList();

Then convert each value in the List to int and add them to an aux List e.g. aux_p_Ids, then use it in the sql query:

UPDATE tbl_Datos SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = @p_Leyenda) WHERE col_Id in aux_p_Ids

Table-Valued Parameters, Column values in table-valued parameters can be accessed using standard Before table-valued parameters were introduced to SQL Server 2008, the Bundle multiple data values into delimited strings or XML documents and When you use a table-valued parameter with a JOIN in a FROM clause, you  We used SqlParameter to parameterize a query in SQL Server. The example here will not work immediately—you must have a database and connection string in your project first. The general idea of using SqlParameter in this way to avoid SQL attacks is useful.


PARAMETERS declaration (Microsoft Access SQL), Go-to-Market Resources For queries that you run regularly, you can use a PARAMETERS declaration to As String Dim strMessage As String Dim intCommand As Integer ' Modify this line to strParm = "PARAMETERS [​Employee Title] CHAR; " ' Define an SQL statement with the parameters ' clause. This second version builds a non-parameterized query using dynamic sql. It is simple to exploit a procedure like this in a SQL Injection Attack. It also does not explicitly tell SQL Server where the parameters are.


[Solved] How to pass string array in SQL parameter to IN clause in , See: Using comma separated value parameter strings in SQL IN clauses[^] a table with these values. after that you can write your query like B) Using SQL Server IN operator with a subquery example. The following query returns a list of product identification numbers of the products located in the store id one and has the quantity greater than or equal to 30: SELECT product_id FROM production.stocks WHERE store_id = 1 AND quantity >= 30;


Parameters, You can include parameter values in a report's URL query string and Mode will return the report's output using those values. The following table summarizes operators on strings: For better performance, when there are two operators that perform the same task, use the case-sensitive one. For faster results, if you are testing for the presence of a symbol or alphanumeric word that is bound by non-alphanumeric characters (or the start or end of a field), use has or in