选择具有多个参数的QUERY SQL [英] SELECT QUERY SQL WITH MANY ARGUMENT

问题描述

我有如下数据列表。

'TED00000，TED00001，TED00002，TED00003'



i希望通过查询sql执行过滤数据，因此数据的标识如下所示：

=========================== ==

NO |身份|描述|

-----------------------------

1 | TED00000 |预订|

-----------------------------

2 | TED00001 |袋子|

-----------------------------

3 | TED00002 |橡皮擦|

-----------------------------

4 | TED00003 |铅笔|

=============================

我使用这个存储过程如下：

  USE  [NewInSys] 
  GO  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROCEDURE  [dbo]。[AdvanceSearch] 
  @sIdentity   VARCHAR （MAX）= NULL， @ sDatabaseId   VARCHAR （MAX）= NULL 
 
  AS  
  SELECT  身份 来自 表 其中标识= @sIdentity  AND  DatabaseID =  @sDatabaseId   
 
  exec  spAdvanceSearch @sTerminalID = N '  TED00000，TED00001，TED00002，TED00003' @sDatabaseID = N '  101' 
 
 
 
但我没有'  t得到我想要的结果。 
执行存储过程中的这一步有什么问题吗？ 
 
解决方案
 
你的查询是做什么的检查身份 IS  N'TED00000，TED00001，TED00002，TED00003'，这显然不是...... 
 
您可能要做的是检查身份是否 IN  N'TED00000，TED00001，TED00002，TED00003'... 
 
请检查如何使用SQL IN运算符：http://www.w3schools.com/sql/sql_in.asp  [ ^ ] 
 
 
看看这里： http://sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure  [ ^ ]。 
 
这是另一种选择： http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/  [ ^ ] 
 
您也可以使用LIKE运算符构造。 IN不适用于参数。您可以使用动态SQL ...等等。看起来你有最完整的列表： http：// stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values  [ ^ ] 
 
 
I have data list like below.

'TED00000,TED00001,TED00002,TED00003'

 

i want to execute through query sql to filter the data, so the identity of the data is shown like below:

=============================

NO | Identity | Description |

-----------------------------

1 | TED00000 | Book |

-----------------------------

2 | TED00001 | Bag |

-----------------------------

3 | TED00002 | Eraser |

-----------------------------

4 | TED00003 | Pencil |

=============================

and i use this Stored Procedure like below :

USE [NewInSys]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[AdvanceSearch]
	@sIdentity VARCHAR(MAX)=NULL, @sDatabaseId VARCHAR(MAX)=NULL
 
AS
SELECT Identity From table Where Identity=@sIdentity AND DatabaseID = @sDatabaseId




then, i try to execute Stored Procedure like below :

exec spAdvanceSearch @sTerminalID=N'TED00000,TED00001,TED00002,TED00003'@sDatabaseID=N'101'


but i didn't get the result as i want.
is there any trouble with this step in the execution of stored procedure?
解决方案
What your query does is check if Identity IS N'TED00000,TED00001,TED00002,TED00003', which obviously not...

What you probably meant to do is check if Identity IN N'TED00000,TED00001,TED00002,TED00003'...

Please check how to use the SQL IN operator: http://www.w3schools.com/sql/sql_in.asp[^]

Take a look here: http://sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure[^]. 

This is an other option: http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/[^]

You can also use a construct with LIKE operator. IN won't work with parameters. You can use dynamic sql... and so on. It looks you have the most complete list here: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values[^]


                        
这篇关于选择具有多个参数的QUERY SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！