程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 如何給SQLSERVER存儲過程傳遞數組參數

如何給SQLSERVER存儲過程傳遞數組參數

編輯:關於SqlServer
 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
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved