如何给SQL SERVER存储过程传递数组参数

时间:2004-07-30 08:00 来源:网管之家bitsCN.com 字体:[ ] 评论:
标签:SQL Server

  Q. How can I pass an array of values to a SQL Server stored-procedure.
  (v1.1 1999.05.14)
  
  A. Basically you can't - SQL Server has no array type - ANSI SQL 92 does not specify array support. But there are various ways around it.
  
  确切的说不行-SQL SERVER没有数组类型,ANSI SQL 92标准也不支持数组。但可用其它的方法来实现。
  
  1. You could simulate an array by passing one or more varchar(255) fields with comma-separated values and then use a WHILE loop with PATINDEX and SUBSTR to extract the values.
  
  1、你可以使用几个VARCHAR(255)字段来模拟数组,字段中用逗号分开各个数据,然后使用循环和PATINDEX和SUBSTR分开这些数据。
  
  2. The more usual way to do this would be to populate a temporary table with the values you need and then use the contents of that table from within the stored-procedure. Example of this below
  
  2、通常这种方法需要为这些数据创建一个临时表,然后在存储过程使用表中的内容。如下例
  
  create procedure mytest @MyParmTempTable varchar(30)
  as
  begin
  -- @MyParmTempTable contains my parameter list...  这个变量是包含参数的表名
  
  -- For simplicity use dynamic sql to copy into a normal temp table...
  
  create table #MyInternalList (
  list_item varchar( 2 ) not null
  )
  
  set nocount on
  
  insert #MyInternalList
  exec ( "select * from " + @MyParmTempTable )
  
  set nocount off
  
  -- It is now easier to join..
  select *
  from sysobjects
  where type in ( select list_item from #MyInternalList )
  
  end
  go
  
  To call..
  
  create table #MyList (
  list_item varchar( 2 ) not null
  )
  insert #MyList values ( 'S' )
  insert #MyList values ( 'U' )
  insert #MyList values ( 'P' )
  
  exec mytest "#MyList"
  
  3. If all you wanted to do was use the array/list as input to an IN clause in a WHERE statement you could use :-
  
  3、如果你想在IN子句里使用输入的数组参数可以这样做:
  
  CREATE PROCEDURE sp_MyProcedure (@MyCommaDelimitedString
  Varchar(255))
  AS
  BEGIN
  EXEC ('SELECT * FROM MYTABLE WHERE MYFIELD IN (' + @MyCommaDelimitedString + ')')
  END
  GO
顶一下(7) 踩一下(1)
同级栏目: Mysql | PHP | Oracle | MSsql | DotNet | JAVA | 其它开发/数据库 |
Top_arrow