Re: Pass Table as a parameter to a function


I wasn't talking about passing many parameters, I was talking about passing one parameter that can have many values. <<

No, it cannot have many values bey definition. Parameters have to be
a scalar value. At one point in ANSI we talked about passing tables
in the SQL/PSM and decided against it. Defining comparisons, the
parameter declarations and constraints, use of VIEWs, etc. made SQL
injection look like a blessing.

The problem with the current method is that it *is* a scalar value.
Reporting Services creates a comma delimited string containing all of
the values selected by the user. This can pose problems when there
are large numbers of values selected. The ability to pass a set would
be a great benefit, and would not require any changes to the way data
is stored.

That aside, your statement that procedures can have many parameters is
meaningless - it doesn't even remotely address the question. Either
you didn't understand the question, or you simply spit out one of your
standard cookie-cutter replies that you felt could best be wedged into
the discussion.

A drop-down list where the user can select more than one value. <<

Gee, I looked all over my SQL Standards and books, but could not find
a drop-down list mentioned. Are you sure that is not part of the
front end and not something which a good programmer would in the
database :)?

The drop-down list *is* in the front end; which is Reporting
Services. Reporting Services uses SQL queries or stored procedures to
pull data. Therefore - stay with me, Joe - those queries/procedures
have to be written to accomidate multiple selections for a drop down
menu.

I strongly suspect that most businesses aren't going to stop using
this functionality just because you say it shouldn't work or that it
isn't standard; nor are they going to wait for Microsoft to create
some additional tier in between Reporting Services and the database;
especially when such a tier is not needed.

This is an EXTREMELY common scenario in the real world. <<

Yes, in the applications side of the real world, not the database.
Hey, there is nothing wrong with being an application programmer. But
it is a different tier.

In this case the only efficient means of getting the data with the
parameters needed is via SQL script or stored procedure; in either
case requiring that SQL be written to handle multiple selections.

Classroom coders who have little to no development experience in the real world tend to panic at the thought of examples that are outside of their limited experience :b <<

LOL! I have been gathering "limited experience" for over 35 years
now! And I have had some influence on RDBMS over the last few
decades. Instead of being a "code monkey" any more, I get called in
to train progammers, design DBs and repair disasters. Part of me
misses the programming discipline of a military weapons or medical
records system. If it screws up even a little or if it goes down, the
wrong people die.

How many years has it been since you've done any real work in the
field?

[snip]

A volunteer programmer did a pull-down list where the package options
were in a comma separated list column in the DB. It made his display
easier. But it messed up the pick list when smaller units were
available. People thought they were asking for 100 units, but it
became 10 units in the backend.

His little violation of 1NF and blending of tiers meant that field
medical personnel had to decide which children would and would not get
antibiotics.

What he did has nothing to do with anything I'm currently talking
about. I haven't said anything about storing comma delimited lists in
columns in the database, or anything like that. Nothing like that is
even necessary. In fact, nothing I am talking about requires any
change whatsoever in how the data is stored.

As you so often do, you are now pulling out and emotionally charged
disaster scenario that is at best superficially related to the topic
at hand. I tell you that it'd be nice to be able to pass a set as a
parameter, and you go into a story about how a bunch of kids didn't
get medicine because some bad programmer made an obvious error that
spread across tiers. Sad story, no doubt, but it has nothing to do
with what we're talking about.

.

Relevant Pages

  • Re: [Info-Ingres] Guardian moves on from Java and Oracle
    ... The application programmer usually doesn't care about the ... Referential integrity and SQL represent an impediment, ... that keep the database from being "unwieldy and slow". ... Essentially re-create the original nested structure? ...
    (comp.databases.ingres)
  • Re: Bitwise OR just like SUM or COUNT
    ... the database, **by definition**. ... I don't look at or directly manipulate metadata in ANY of the SQL ... do when I am wearing my programmer hat. ... COBOL & SQL program from an EBCDIC machine ...
    (microsoft.public.sqlserver.programming)
  • Re: software needed to input data
    ... ENTERPRISE MANAGER give you a simple one table at a time ... Your programmer might have proprietary rights to the ... >If you are database savvy, you can always get into SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Ad Hoc Query in C#
    ... I always like to put as much logic in the database as possible, ... tradeoff is the programmer cannot see what is going on in the database. ... or just parameterize his string sql query. ... > Putting logic like this in a stored procedure isn't alway to my liking ...
    (microsoft.public.dotnet.general)
  • Re: Embedded SQL in C#
    ... Which is one of the reasons embedding non-trivial SQL in applciation code, ... SQL statements belong in the database, ... SQL in the application tier is simple: ...
    (microsoft.public.dotnet.languages.csharp)