Parameters best practice

Folks, What is the recommended best practice for the following?
Let’s say we are in the pubs database and we want to return some employee
information via a stored proc with an optional parameter of last name. The
issue at hand is how do you structure the stored proc to allow multiple last
names to be provided? It seems that some form of
SELECT * FROM [employee] WHERE [lname] in (@.lastNames)
would do the trick, but no.
Building a sql statement will work but that kind of defeats the value of
using parameters to prevent injection.
One and none provided are easy. Executing the stored proc multiple times
does not seem like the best answer.
Thanks…ChucIt seems as if I quote Dejan on a daily basis:
http://solidqualitylearning.com/blo.../10/22/200.aspx
Since the function mentioned in Dejan's blog returns a table of values,
you'd use it in a join to your table.
ML|||http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Why do you think that your imaginary syntax "lname IN (@.lastNames"
makes sense? A parameter is scalar, SQL is a compiled language!! Why
did you use square brackets instead of non-proprietary syntax?
names to be provided? <<
This is a common Newbie question. This shows that you don't know what
a scalar parameter is. This is MUCH worse than not knowing SQL. This
is a fundamental programming concept that you should learn in the first
w of any language class.
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. This is not just an SQL problem; this is a basic
misunderstanding of programming of 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 then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
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.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", 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
FROM ( VALUES (p1), (p2), .., (pN))
AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
You will need to use SELECT ..UNION ALL in T-SQL dialect for this until
MS comes up to standards that a over a decade old.
3) The right way! You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems. A good optimizer will not need the
SELECT DISTINCT, just a SELECT.|||Great attitude. Check it at the door next time...
"--CELKO--" wrote:

> Why do you think that your imaginary syntax "lname IN (@.lastNames"
> makes sense? A parameter is scalar, SQL is a compiled language!! Why
> did you use square brackets instead of non-proprietary syntax?
>
> names to be provided? <<
> This is a common Newbie question. This shows that you don't know what
> a scalar parameter is. This is MUCH worse than not knowing SQL. This
> is a fundamental programming concept that you should learn in the first
> w of any language class.
> 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. This is not just an SQL problem; this is a basic
> misunderstanding of programming of 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 then write:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
> 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.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array", 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
> FROM ( VALUES (p1), (p2), .., (pN))
> AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> You will need to use SELECT ..UNION ALL in T-SQL dialect for this until
> MS comes up to standards that a over a decade old.
> 3) The right way! You load the Parmlist table with values so that each
> value is validated by the SQL engine, subject to more constraints and
> you have no SQL injection problems. A good optimizer will not need the
> SELECT DISTINCT, just a SELECT.
>