如何在SQL查询中传递多值参数 [英] How to Passing Multi Value Parameter in SQL Query

问题描述

我有一个问题：

执行''选择*从接收代码输入（''+ @pCode +''）''


@pCode将包含多个字符串参数，例如：A1，A2，A3

如何编写参数，我尝试使用：

set @ pCode =''A1''，''A2''，''A3''

但收到错误：''附近语法不正确'''

请帮助我


谢谢

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

推荐答案

Resant，这是答案。


1.设置@pCode =''''''A1'''''''''A2'''''''''''''''''' >

2.执行（''选择*从接收代码输入（''+ @pCode +''）''）


祝你好运。


source4book.com

http ：//www.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

Resant（重新***） ***@yahoo.com）写道：

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

我有一个查询：
执行''选择*从接收代码输入（''+ @pCode +''）''

@pCode将包含多个字符串参数，例如：A1，A2，A3

如何编写该参数，我尝试使用：
set @pCode =''A1''，''A2''，''A3''
但是收到错误：''，''

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是对存储过程的调用。在parathenses中包含

表达式以获得动态SQL：

EXEC（@sql）


但你应该不要使用动态SQL来做这种事情。看看

at
a的_blank> http://www.sommarskog.se/arrays-in-s...ist-of-strings 更好地解决了这个问题。

-

Erland Sommarskog，SQL Server MVP， es****@sommarskog.se


SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp

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

1）危险的，缓慢的kludge是使用动态SQL并承认任何

随机的furure用户是比你更好的程序员。它由

新手使用，他们不懂SQL，甚至不懂编译语言。

字符串是一个字符串;它是一个标量值，就像任何其他参数一样;

它不是代码。这不仅仅是一个SQL问题;这是对原理编程的基本误解。 。


2）将parmeter列表传递给存储过程可以通过

将它们放入带分隔符的字符串中。我喜欢使用

传统逗号。让我们假设你有一个完整的表格

这样的参数列表：


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''）;

等


这将是获取输出的表格，格式为

原始键列和每行一个参数。


CREATE TABLE Parmlist

（keycol CHAR（10）NOT NULL PRIMARY KEY，

parm INTEGER NOT NULL）;


它使如果输入字符串中的列表以逗号开头和结尾

，则生活会更轻松。你需要一个序列表 - 一个

标准的SQL编程技巧，现在，查询，用SQL-92语法

（翻译成你的本地方言）：


INSERT INTO ParmList（keycol，parm）

SELECT keycol，

CAST（SUBSTRING（I1.input_string
）
来自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）=

''，''

和SUBSTRING（''，''|| I1.input_string ||''，''来自S2.seq FOR 1）=

''，''

AND S1.seq< S2.seq

GROUP BY I1.keycol，I1.input_string，S1.seq;


序列的S1和S2副本用于定位包围对

逗号，并且它们之间的整个子串是

提取并在一个非程序步骤中作为整数转换。诀窍

是为了确保包围对的右手逗号是最接近第一个逗号的

。


然后你可以写：


SELECT *

FROM Foobar

WHERE x IN（SELECT parm FROM Parmlist WHERE key_col = ：某事）;


嘿，我可以用最好的查询在单个查询中写出kludges，但是我没有b $ b。你需要至少编写一个例程来清除字符串中的
空白和非数字，处理浮点和

十进制表示法等。基本上，你必须在

SQL中编写一部分编译器。 Yeeeech！或者决定你不想拥有数据完整性，

这是大多数新手在实践中所做的事情。


3）正确的方法是使用表格使用IN（）谓词，你设置了

带有假数组的过程声明，就像在SQL / PSM中一样

（翻译成你的本地方言） ：


CREATE PROCEDURE Foobar（<其他参数>，IN p1 INTEGER，IN p2

INTEGER，.. IN pN INTEGER） - 默认缺失值到NULLs

AS

SELECT foo，bar，blah，yadda，...

FROM Floob

WHERE my_col

IN（SELECT DISTINCT parm

FROM（VALUES（p1），（p2），..，（pN））

AS ParmList （parm）

其中parm IS NOT NULL

AND<其他条件>）

AND< more predicates>


您使用值加载Parmlist表，以便SQL引擎验证每个值

，受更多约束并且您没有SQL
注射问题。一个好的优化器不需要SELECT

DISTINCT，只需一个SELECT。

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.

这篇关于如何在SQL查询中传递多值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！