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

王朝mssql·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航