WHERE IN() with parameters

[Guest]

Yes a more elegant way would be to insert each "parameter" as new DataRows
in an in-memory DataTable, then replace your IN with a JOIN.
Jeff

I'll consider try that just for the fun of it, and see how practical it is
versus string-concatenation

Thanks for the responses Jeff

 

[Jeff Dillon]

then try bite me for fun

I'll consider try that just for the fun of it, and see how practical it is
versus string-concatenation

Thanks for the responses Jeff

 

[Guest]

The classes in the System.Data.SqlClient namespace work with Access? Since
when?
(http://msdn.microsoft.com/library/d.../en-us/cpref/html/frlrfsystemdatasqlclient.as
http://msdn.microsoft.com/library/d...systemdatasqlclientsqlconnectionclasstopic.as
http://searchvb.techtarget.com/ateQuestionNResponse/0,289625,sid8_cid571548_tax293673,00.html)

"CREATE PROCEDURE" works with Access? Since when?
(http://dbforums.com/t524759.html)
(ok, I found an example where CREATE PROCEDURE created an entry in a hidden
table in access and included it in the list of data sources but it was not
usable)

If you can provide an example of actual, functional code, I will gladly
concede the point.

You do imply that your code answers his question of easily parameterizing
dynamically built SQL. It does not use parameters therefore, it does not.

You might consider reading the other posts before you accuse me of not
having the brains to post a solution. I answered under the original thread
(at approximately the same time you responded directly) suggesting that he
use StringBuilders and some overloaded methods to not only dynamically build
a parameterized query but also to add the parameters with their values to it.

Unlike you, I don't insult your intelligence with personal attacks such as
"you didn't have the brains..." and "...for reasons you couldn't possibly
understand..." despite the fact that you posted irrelevant code and
continually assert statements that just aren't true. Also unlike you, in my
post I don't claim that my method is "the best" then refuse to refute any
claim about poor application design in terms of performance and security and
resort to personal attacks (see above) and statements such as:
"I hear this performance nonsense all the time"
"I code in the real world, for real customers" [Is this to assume that a
Fortune 500 company and it's supply base (900+ active, individual users)
isn't a "real customer?"]

How is optimizing processor time by minimizing useless processing
"academically insightful but practically stupid?" It is, simply put, good
practice. Also consider that many enterprises that use mainframes (and other
platforms) do indeed charge back the requesting organization for processor
time. Before you attack me [again] and lecture me about how good performance
is nonsense, understand that I'm not saying that this is the case with your
customers because I don't know if it is or not. I am saying though, that if
this is the case, you're costing them more money than they think...what's
worse is that if this is the case, they probably don't even realize it.

I too, do some freelance work in addition to my full time job (at above
mentioned company) and have found that the majority of customers are willing
to live with some degree of poor performance not because they have accepted
it but because they don't know any better. This is not to say that they're
"stupid" so don't take it as such. This is simply saying that they accept it
because they think they have to because they do not know the intracacies of
application design and therefore do not know any better. They often believe
that they do not have a choice. These customers simply do not care to learn
that much detail (I'm not saying that they should have to) but that is no
reason to provide them with a substandard product. It is my job to provide
my customer with a solid, high quality solution.

As support for my case I provide this simple example: I have a customer
that was willing to pay $40/hour to someone to design a web site that
consisted of graphics lifted from generic image galleries (ie: spinning gold
"welcome" signs and "drifting" envelopes) and Word Art. $40/hour is a lot of
money for low quality. Furthermore, this customer would most likely end up
paying even more later when he decided that the site should be redesigned
because it did not portray a professional image.

This particular customer is extremely intelligent and highly respected in
his field. He has been heavily involved in policy setting for a division of
Legal Services in Michigan and has been a ranking official in the county
court system in five Michigan counties. My customer has been engaged in
family law for nearly 30 years. He is, by far, not "stupid." He just does
not understand web development (again, nor should he have to) because it is
outside of the scope of his concerns.

The same holds true for all types of development be it for Web design or for
desktop applications. I respect my real world (to borrow another of your
phases) enough to provide them with a quality product. I will not penalize
them with a substandard product simply because they do not know the
technology. People hire me to provide them with the best product that I can
and I work for that money and their respect.

Until you can offer some evidence about how you've given "the best" (again,
your claim) solution rather than resort to personal attacks and other such
statements, you may want to take your own advice and "don't bother."

Until then, good luck to you, sir.

I don't know why I bother, but for a brief moment, I will.

1. The concept works equally well against ms access as it does against sql
server. I'm sure Tor would understand that.
2. I code in the real world, for real customers. I have made a very good
living doing it, and my customers wouldn't leave me for a second.
3. No (even though you haven't the courtesy to name it), I don't care about
SQL injection. It won't happen in my environment for reasons you can't
possibly understand.
4. The recompile is meaningless. My customers have no problem with the
daunting extra 10 seconds I cost them.
5. There is no concurrency issue, as they are locked out for those delicate
15 (total) seconds.
6.

Every time this code is executed, both your application and database
server
are doing unnecessary work. It is obvious by the tone of your response
that
you do not value your users' (see also: customer) time since you have no
problem with making them wait unnecessarily (e.g.: wasting their time)
because of poor application design. If this server is being used by other
applications, you are tying up resources that are probably better utilized
by
other processes.

Think about how academically insightful but practically stupid that is.
7. I imply nothing to Tor - I gave him a way of dealing with a common
problem.

Finally, you didn't have the brains to come up with the practical solution
that I did, so you have little else to do but debunk it.

Try the real world, Dave; you might have some success at it.

Since when does poor performance equate to good application design (did
you
not say that your method is "the best way")? When did good application
design suddenly transform into nonsense?

Perhaps you hear about this "nonsense" "all the time" because you need to
learn some lessons about its importance in application design. In the
"practical world," 15 seconds of unnecessary wait time is needlessly
wasted
time (for both the computer and the human) that could have been better
spent
elsewhere. I'm not at all arguing that entirely eliminating wait time is
possible but it should always be minimized. (Also, don't forget to
include
network latency in determining total wait time.)

Every time this code is executed, both your application and database
server
are doing unnecessary work. It is obvious by the tone of your response
that
you do not value your users' (see also: customer) time since you have no
problem with making them wait unnecessarily (e.g.: wasting their time)
because of poor application design. If this server is being used by other
applications, you are tying up resources that are probably better utilized
by
other processes.

You say that your method forces a recompile because the SP is different
every time but in reality, this method does not force a recopile but
rather
forces a compile because it isn't that the SP is different but is a
different
SP altogether. (you dropped it, remember?) It may have a common name but
it
is a different SP.

By posting this code in response to Tor's question, you [indirectly] claim
that this method allows for a variable parameter list, but your SP isn't
parameterized at all. Tor wanted to find an easy way to parameterize a
dynamically generated IN clause of a query against an Access database.
Completely ignoring the fact that your code is specific to SQL Server
(and,
as such, completely irrelevant to the question), where in "CREATE
PROCEDURE
sp_createa_rsummary AS ..." is a parameter list supplied? This method
doesn't allow for a variable parameter list at all since you're still
relying
solely on string concatenation.

Of course, relying solely on string concatenation rather than an actual
parameterized query opens up potentially larger problems but I'll leave it
up
to you to discover what they are...perhaps your code is immune to them
because this is "the best way?"

Another point is the issue of concurrency. On top of the added overhead
from additional executions, suppose you have two users that coincidentally
execute this code within a fraction of a second of each other. User A's
execution has just completed executing the code to create the procedure
but
has not yet executed the code to execute it (the SP). While User A's
execution is in the state I just described, what happens when User B's
code
executes the drop procedure code?

Since the code is in a try block, the catch code is executed and a message
box is displayed telling the user that the procedure couldn't be found
then
continues to execute because you don't have any code to exit the function.
Following the execution path, the SP for altering the table executes and
throws another error which is caught and displays another error message.
The
function then assumes that everything worked correctly (which it didn't)
and
execution continues, most likely providing the user with incorrect
results!

Is it unlikely for this scenario to occur? Sure, but it is conceivable.
This "solution" simply does not scale. As the user base grows, so does
the
probability that this problem will surface.

Finally, why not use a view for the common part of the query? All that
you're dynamically generating is the IN clause.

Hi Dave,

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.
Yes, you're correct. I lose a full second on this! Damn!

the SP is only ever being executed once. SPs are always compiled for
their first execution so on top of the hit from updating the system
tables, you're forcing a compile every time the SP executes.
That's because the sp is different every time. This too costs me, after
all, as you correctly point out, it's not compiled yet. This also can
cost
me anywhere from 2 - 10 seconds! Good God!

Dave, I hear this performance nonsense all the time. In the practical
world, a function like this runs in 15 seconds. Give me a break.

Bernie

Yikes!!!
The code you provided is just laced with performance killers. Dropping
and recreating stored procedures means updates to system tables.
Additionally, the SP is only ever being executed once. SPs are always
compiled for their first execution so on top of the hit from updating
the
system tables, you're forcing a compile every time the SP executes.

I would strongly suggest reading
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
for some information about using SPs that execute dynamic SQL. Also
note
that using the Prepare() method of the SqlCommand class has a similar
effect. Both of these methods avoid the constant updates to the system
tables and will improve the operation of your application.

On another, possibly moot point, this code doesn't apply to Tor's
question. This code is specific to SQL Server while he's asking about
the
classes contained in System.Data.OleDb (OleDbCommand, OleDbParameter,
etc...) and is referring to an Access database.


Hi Tor,

Actually, I've found that the best way to do this is indeed to pass an
array. Now I know you can't, but you can, in effect, if you create
the
sp 'on the fly'. What I do is I first delete the sp; then I create a
bulkstring to contain my array - '12345', '34225', '957433', etc.
Then I
construct the sp, create it from the vb code, and run it. Below is an
example of one such function I use.

HTH,

Bernie Yaeger
Public Function createa_rsummary(ByVal marraylist As ArrayList, ByVal
oconn As SqlConnection) As Integer

createa_rsummary = 0

Dim i As Integer

Dim bulkstring As String

Dim acount As Integer = 0

For i = 0 To marraylist.Count - 1

acount += 1

bulkstring += Chr(39) & Trim(marraylist(i)) & Chr(39) & ","

If i <> marraylist.Count - 1 Then ' ie, it isn't the last item

If acount > 20 Then

acount = 0

bulkstring += vbCrLf

End If

End If

Next

bulkstring = Mid(bulkstring, 1, bulkstring.Length - 1) & ")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_createa_rsummary", oconn) ' drop the
sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_createa_rsummary AS " _

& "if exists (select * from information_schema.tables where table_name
=
" _

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as
under31, " _

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate()
then
balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate()
then
balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate()
then
balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _

& " into a_rsumtable from a_r where imcacct" _

& " in (" & bulkstring & vbCrLf _

& "order by pubcode, imcacct"

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_createa_rsummary", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim acmd As New SqlCommand

acmd = New SqlCommand("sp_altera_rsumtable", oconn) ' give new table a
pk

acmd.CommandType = CommandType.StoredProcedure

Try

acmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

' we now have a_rsumtable for only the selected accounts

createa_rsummary = 1

End Function

"Tor Martin Halvorsen" <[email protected]>
wrote in message
Hi all,

Just for fun, I was investigating wheither I could use
OleDbParameters
in
queries with WHERE IN() clauses.

Given the code:
...
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
...

After some hours with head scratching and banging I found that for
every
record I needed to get I had to add a new question-mark to the IN()
clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the
clause.

So is there a more elegant way to use OleDbParameters with IN
clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.

 

[Guest]

It is possible to do this with oracle and a stored procedure; I'm doing it
currently with Oracle9i and the ODP.NET driver. I haven't checked if this is
possible with SQL Server or other versions of Oracle.

I'm not saying this is the best or only way to use an array in a query;
however, it is ONE of the ways. By all means, try everyone else's suggestions
first before tackling this confusing bit of code. Also, I suggest you find
someone familiar with Oracle who can answer your questions about the code
below. Use the code as a basis for your own code, cut n' paste may not work
without extensive modification.


using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

//I like to start with an ArrayList because it's easy to add new items.
ArrayList arrLstItems = new ArrayList();
for (int i = 0; i< 10; i++)
{
arrItems.Add(i);
}
string[] arrItems = (string[])arrLstItems.ToArray(typeof(System.String));

oraConn.ConnectionString = "YourOracleConnectionString";
oraConn.Open();
OracleCommand cmd = new OracleCommand("OraPackage.proc_YourProcedure",
oraConn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("p_ItemIn", OracleDbType.Varchar2, 50,
ParameterDirection.Input);
cmd.Parameters.Add("p_refcur", OracleDbType.RefCursor, DBNull.Value,
ParameterDirection.Output);

cmd.Parameters["p_ItemIn"].CollectionType =
OracleCollectionType.PLSQLAssociativeArray;
cmd.Parameters["p_ItemIn"].Value = arrItems;
cmd.Parameters["p_ItemIn"].ArrayBindStatus = new
OracleParameterStatus[arrItems.Length];
cmd.Parameters["p_ItemIn"].Size = arrItems.Length;

cmd.ExecuteNonQuery();

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable("Items");
dt.BeginLoadData();
da.Fill(dt);
dt.EndLoadData();
oraConn.Close();
//You now have a usable DataTable.


Before the code will work, you'll have to setup oracle to use it.
You'll need an oracle type, package body with the plsqlarray, the procedure
you're calling, and a function to convert the plsqlarray to an array that
oracle likes.

In oracle, you'll need to create an Oracle Type.
This is necessary because oracle actually supports several different kinds
of arrays. It can accept a parameter of plsqlarray, but can select from an
oracletype (nested table).
the command to create the oracle type is:
TYPE "TBLOFVARCHAR50" AS TABLE OF VARCHAR2(50)


Your oracle package would look like this (I've edited my working code from
JDeveloper):
PACKAGE "OraPackage" AS
--easy to manage refcursor
TYPE REFCUR IS REF CURSOR;

--PLSQLARRAY, how oracle makes arrays possible. (not easy, just possible)
TYPE ASARRAY IS TABLE OF VARCHAR2(50) index by BINARY_INTEGER;

procedure proc_YourProcedure
(
p_ItemIn in ASARRAY,
p_refcur OUT refcur
);

function VarcharArr2tbl(p_arr IN ASARRAY) return tblOfVarchar50;
END;

PACKAGE BODY "OraPackage" AS
procedure proc_YourProcedure
(
p_ItemIn in ASARRAY,
p_refcur OUT refcur
)
AS
v_tmparray tblofvarchar50;
BEGIN
--Oracle won't let a package select from a plsqlarray, it has to be an
Oracle Type.
v_tmparray := varcharArr2tbl(p_ItemIn);

--here's the best part. selecting from IN an array!
OPEN p_refcur FOR
SELECT title, txt
FROM tblFoo
WHERE
bar IN (select cast(column_value as VARCHAR2(50)) from
TABLE(CAST(v_tmparray AS tblofvarchar50)));
end proc_YourProcedure;

--converts the plsqlarray to an oracle type
function VarcharArr2tbl(p_arr IN ASARRAY) return tblOfVarchar50
as
l_data tblOfVarchar50 := tblOfVarchar50();
begin
FOR i IN 1..p_arr.LAST
LOOP
l_data.EXTEND;
l_data( l_data.count ) := p_arr(i);
END LOOP;
return l_data;
end;
END;