This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w
of any SQL language class. SQL is not your original
procedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w
of any SQL language class. SQL is not your original
> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w
of any SQL language class. SQL is not your original
> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Kevin Rodgers wrote: