Geek asks and answers

Filter SQL queries by parameter list

I have a query in which I want to return all rows associated with a list of values. You can write this very simply:

select * from TableA where ColumnB in (1, 2, 3, 5)

I could generate this request in C # and execute it. However, this is clearly not ideal, since it does not use parameters, it will suffer when trying to cache query plans and is clearly vulnerable to an SQL injection attack.

An alternative is to write this as:

select * from TableA where ColumnB = @value

This can be done multiple times using C #, however this will result in N database hits.

The only alternative that I see is to create a temporary table and join it in this way, but I do not see this point of view in this, because it will be more complex and will have the same limitations as the first option.

I use SQL Server and OLDB, making a query is not a problem. I am trying to create the most efficient process.

Which of these three methods is more effective? Did I miss an alternative?

+5
source share
3 answers

Assuming SQL Server 2008 or newer in SQL Server, create the table type once:

CREATE TYPE dbo.ColumnBValues AS TABLE
(
  ColumnB INT
);

Then a procedure is stored that accepts this type of input:

CREATE PROCEDURE dbo.whatever
  @ColumnBValues dbo.ColumnBValues READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT A.* FROM dbo.TableA AS A
    INNER JOIN @ColumnBValues AS c
    ON A.ColumnB = c.ColumnB;
END
GO

Now in C # create a DataTable and pass this as a parameter to the stored procedure:

DataTable cbv = new DataTable();
cbv.Columns.Add(new DataColumn("ColumnB"));

// in a loop from a collection, presumably:
cbv.Rows.Add(someThing.someValue);

using (connectionObject)
{
    SqlCommand cmd        = new SqlCommand("dbo.whatever", connectionObject);
    cmd.CommandType       = CommandType.StoredProcedure;
    SqlParameter cbvParam = cmd.Parameters.AddWithValue("@ColumnBValues", cbv);
    cbvParam.SqlDbType    = SqlDbType.Structured;
    //cmd.Execute...;
}

(You might want to make the type more universal, I named it specifically so that it makes it clear what it does.)

+4
source

You can also use multiple result sets and submit a query request as follows:

select * from TableA where ColumnB = @value0
select * from TableA where ColumnB = @value1
select * from TableA where ColumnB = @value2
...
select * from TableA where ColumnB = @valuen

. , .

+2

:

String csvString = "1, 2, 3, 5"; // Built the list somehow, don't forget escaping
String query = "select * from TableA where ColumnB in (" + csvString + ")";

, , Sql Injection, csvString.

BTW, MS SQL SQL, .

0

More articles:


All Articles