How to Passing Multi Value Parameter in SQL Query

[Resant]

I have a query :
Exec 'Select * From Receiving Where Code In (' + @pCode + ')'

@pCode will contain more than one string parameter, eg : A1, A2, A3

How can i write that parameters, I try use :
set @pCode='A1','A2','A3'
but get an error : Incorrect syntax near ','
Please help me

Thanks

[Source4book.com]

Resant, here is the answer.

1. set @pCode='''A1'',''A2'',''A3'''

2. Exec ('Select * From Receiving Where Code In (' + @pCode + ')')

Good luck.

source4book.com

http://www.source4book.com

[Erland Sommarskog]

Resant (re******@yahoo.com) writes:

I have a query :
Exec 'Select * From Receiving Where Code In (' + @pCode + ')'

@pCode will contain more than one string parameter, eg : A1, A2, A3

How can i write that parameters, I try use :
set @pCode='A1','A2','A3'
but get an error : Incorrect syntax near ','

EXEC without parentheses is a call to a stored procedure. Wrap the
expression in parathenses to get dynamic SQL:

EXEC(@sql)

But you should not use dynamic SQL for this sort of thing. Have a look
at http://www.sommarskog.se/arrays-in-s...ist-of-strings for
a much better way to approach this problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

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 PRIMARY KEY,
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 in single queries 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
AS
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>

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.

[Resant]

--CELKO--
I'm interested with method no.3 that use IN () predicate, but still
confused. Would you like to give an example please. That query is too
strange for me.

Thank you

[Erland Sommarskog]

Resant (re******@yahoo.com) writes:

--CELKO--
I'm interested with method no.3 that use IN () predicate, but still
confused. Would you like to give an example please. That query is too
strange for me.

You are not the only one. One would think that a person Joe Celko that
scorns other people for using proprietary features, would stay clear from
that himself, but oh no. That syntax exists in ANSI SQL, but that does
not mean that it works everywhere. Specifically, that syntax does not
work on SQL Server. As a general advice for new users, you do best in
ignore Celko's postings, as they are more likely to be confusing and
insulting than to be helpful.

Did you look at the link that I posted? That's a solution that does not
use IN, but it is one that is effecient on SQL Server.
ttp://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

That is a "cut & paste" I use which is written in Standard SQL for a
lot of different newsgroups. The VALUES clause constructs a column or
a full table and the AS gives the table and its columns names to
reference. In SQL Server dialect, you would use:

(SELECT p1
UNION ALL
SELECT p2
UNION ALL
..
SELECT pN) AS Parmlist (parm)

Which I find to be much stranger since a SELECT must have a FROM in
Standard SQL and just about any other dialect. I have no idea why MS
has resisted row constructors in so many places in T-SQL when the
engine has the ability.

[Resant]

> Did you look at the link that I posted? That's a solution that does not

use IN, but it is one that is effecient on SQL Server.
ttp://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings.

Yes I did, it's more reasonable for me.
But if I intend to give access for user to the table that is accessed
in my stored procedure, it should be ok if I use dynamic sql as my
first posting, isn't it?

Thank you

[Erland Sommarskog]

Resant (re******@yahoo.com) writes:

Yes I did, it's more reasonable for me.
But if I intend to give access for user to the table that is accessed
in my stored procedure, it should be ok if I use dynamic sql as my
first posting, isn't it?

There are really no advantages with dynamic SQL for this kind of problem.
Of course, for smaller lists it does not matter. But if the lists are
very long, you get very poor performance with dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Akash Kava]

Hi,

Even I was looking for same and I found a great method to overcome your
problem. Trick is not to use IN clause and use the strings functions.


So the easiest way to think is wherever you write

( Column IN (@Params) )

you just replace above peice of code with

( CHARINDEX( QUOTENAME( Column , '''' ), @Params)<> -1 )

Background:

if you want to find out wheather "b" exists in a,b,c or not use this.

@Params = "a,b,c"

CHARINDEX( Column , @Params ) <> -1

this will turn on true if column has a , b or c in it.

here problem may araise if you have set like aa,ab,bc
and you are searching for only b which doesnt exist..

here you can quote your string like

@Params = "'a','b','c'"

and change sql command as

CHARINDEX( QUOTENAME( Column , '''' ) , @Params )<> -1

So the easiest way to think is wherever you write

( Column IN (@Params) )

you just replace above peice of code with

( CHARINDEX( QUOTENAME( Column , '''' ), @Params)<> -1 )

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

[posted and mailed, please reply in news]

Akash Kava (ac****@hotmail.com) writes:

Even I was looking for same and I found a great method to overcome your
problem. Trick is not to use IN clause and use the strings functions.

So the easiest way to think is wherever you write

( Column IN (@Params) )

you just replace above peice of code with

( CHARINDEX( QUOTENAME( Column , '''' ), @Params)<> -1 )
Background:

if you want to find out wheather "b" exists in a,b,c or not use this.

@Params = "a,b,c"

CHARINDEX( Column , @Params ) <> -1

this will turn on true if column has a , b or c in it.

Warning! This method have serious performance problems, even with
short lists. First of all any index on Column will not be used.
But even if the column is not indexed - in which case this does not
matter - this operation is very expensive. When I ran performance
tests a couple of years ago on various methods, a list that was
around 255 character long cause a search to take 40 seconds!

That test, as well as several other - and better! - methods to tackle
this problem is described in an article on my web site. See
http://www.sommarskog.se/arrays-in-sql.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp