开发学院数据库MSSQL Server 如何给SQLSERVER存储过程传递数组参数 阅读

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

 2007-11-11 09:01:13 来源:WEB开发网   
核心提示:Q. How can I pass an array of values to a sql server(WINDOWS平台上强大的数据库平台) stored-procedure.(v1.1 1999.05.14)A. Basically you can't - sql server(WINDOWS平台上强大的

  Q. How can I pass an array of values to a sql server(WINDOWS平台上强大的数据库平台) stored-procedure.
  (v1.1 1999.05.14)
  
  A. Basically you can't - sql server(WINDOWS平台上强大的数据库平台) has no array type - ANSI SQL 92 does not specify array support. But there are various ways around it.
  
  确切的说不行-sql server(WINDOWS平台上强大的数据库平台)没有数组类型,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

Tags:如何 SQLSERVER 存储

编辑录入:coldstar [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接