如何给SQL SERVER存储过程传递数组参数
来源:互联网 发布:java中包的作用 编辑:程序博客网 时间:2024/05/06 07:53
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
(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
- 如何给SQL SERVER存储过程传递数组参数
- 要给SQL SERVER存储过程传递数组参数,应怎么办?
- 如何给SQLSERVER存储过程传递数组参数
- 如何向sql server存储过程传递一个数组
- SQL Server存储过程中如何使用数组参数
- 给SQL Server存储过程,传送数组参数(多个参数)的变通办法
- SQL Server存储过程数组参数
- SQL Server存储过程数组参数
- SQL Server存储过程数组参数
- 用JDBC给存储过程传递数组类型的参数
- java传递数组类型参数给oracle存储过程
- 给SQL Server存储过程,传送数组参数的变通办法
- SSRS如何将多值参数传递给存储过程
- SQL Server 存储过程--传递数组、使用循环
- Sql 存储过程传递参数
- SQL Server存储过程 对数组参数的循环处理
- SQL Server存储过程中使用"数组参数"
- Sql Server存储过程数组类型参数的变通方法
- .Net平台开发的技术规范与实践精华总结
- OracleoraHome92Agent service服务启动后又停止了的解决办法
- javascript清除列表选单的内容
- 测试
- ORA-12545:因目标主机或对象不存在,连接失败的解决办法
- 如何给SQL SERVER存储过程传递数组参数
- log4j详解
- 怎样用C#实现完整文档打印功能
- 解决方案:用户 'sa' 登录失败。原因: 未与信任 SQL Server 连接相关联
- VC++ Proc
- Computer Love
- 图片地址防盗链,通过IHttpHandler实现
- ASP接收单个表单的多个提交按钮
- 数据结构算法