sql存儲過程數組參數

① 如何給存儲過程，傳一個數組參數

方法一 分割

例:通過sql Server存儲過程傳送數組參數刪除多條記錄

eg. ID 值為'1,2,3' 以下存儲過程就是刪除表中id號為1,2,3的記錄：

CREATE PROCEDURE DeleteNews
@ID nvarchar(500)
as
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TId int
Set @PointerPrev=1

while (@PointerPrev < LEN(@ID))
Begin
Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)
if(@PointerCurr>0)
Begin
set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int)
Delete from News whereID=@TID
SET @PointerPrev = @PointerCurr+1
End
else
Break
End
--刪除最後一個,因為最後一個後面沒有逗號,所以在循環中跳出,需另外再刪除
set @TId=cast(SUBSTRING(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int)
Delete from News whereID=@TID
GO

這個方法麻煩不？於是又有另外一種方法——臨時表

方法二 Table對象

傳3個參數，都是數組形式還有時間類型用存儲過程更新

@Oid = 1,2,3,4

@Did = 111,222,333,444

@DateArr = '2007-1-1,2007-1-2,2007-1-3,2007-1-4'

CREATE proc Test999

@Oid nvarchar(1000)--ID1

,@Did nvarchar(1000)--ID2

,@DateArr nvarchar(1000)--日期

AS

DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000)

set @id1s=@Oid

set @id2s=@Did

set @dates = @DateArr

-- 調用函數實現處理

SELECT @id1s=@id1s, @id2s=@id2s,@dates = @dates

UPDATE A SET terminate_time = B.dt

FROM [Table] A,(

SELECT

id1 = CONVERT(int, Desk_id.value),

id2 = CONVERT(int, room_id.value),

dt = CONVERT(datetime, terminate_time.value)

FROM dbo.f_splitstr(@id1s) Desk_id, dbo.f_splitstr(@id2s) room_id, dbo.f_splitstr(@dates) terminate_time

WHERE Desk_id.id = room_id.id

AND Desk_id.id = terminate_time.id

) B

WHERE A.Desk_id = B.ID1 AND A.room_id = B.ID2

GO這個還用到一個函數f_splitstr

CREATE FUNCTION dbo.f_splitstr(

@str varchar(8000)

)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))

AS

BEGIN

DECLARE @pos int

SET @pos = CHARINDEX(',', @str)

WHILE @pos > 0

BEGIN

INSERT @r(value) VALUES(LEFT(@str, @pos - 1))

SELECT

@str = STUFF(@str, 1, @pos, ''),

@pos = CHARINDEX(',', @str)

IF @str > ''

INSERT @r(value) VALUES(@str)

RETURN

這個方法更加可怕~~~輾轉網路，找到了一個還不錯的方法，用OPENXML，這個SQL2000就支持了。

方法三 xml

應該用SQL2000 OpenXML更簡單，效率更高，代碼更可讀：

CREATE Procere [dbo].[ProctListUpdateSpecialList]
(
@ProctId_Array NVARCHAR(2000),
@MoleId INT
)

AS

delete from ProctListSpecial whereMoleId=@MoleId

-- If empty, return
IF (@ProctId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProctId_Array))) = 0)
RETURN

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @ProctId_Array

Insert into ProctListSpecial (MoleId,ProctId)
Select
@MoleId,C.[ProctId]
FROM
OPENXML(@idoc, '/Procts/Proct', 3)
with (ProctId int ) as C
where
C.[ProctId] is not null

EXEC sp_xml_removedocument @idoc

哇，看起來還是很復雜的說。有木有更好的辦法呢？

既然是OPENXML，為啥不用XML呢？於是查到，SQL2005以上都支持XML。Good，找到一片天了。

利用SQL2005的XML/XQuery功能，可以很方便的解決傳數組參數的問題。

declare @xml xml
set @xml = '<?xml version="1.0"?>
<ArrayOfInt>
<int>1</int>
<int>2</int>
<int>3</int>
</ArrayOfInt>'

select N.value( '(text())[1]','int' ) RoomId from @xml.nodes('/ArrayOfInt/int') V(N)

結果就是

註：上面的數據類型為XML

這樣就可以給存儲過程傳一個集合了，一般是數組，比如主鍵的集合。然後可用通過主鍵集合來查詢記錄。

客戶端可用使用序列化，把list轉化成xml。不過在序列化過程中，遇到了一些小麻煩。

1. .net默認是utf-16，SQL只認識utf-8

2. 出現很討厭的xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

可以用我這個類

public static class SerializeHelper
{
private static readonly XmlSerializerNamespaces Namespaces = new XmlSerializerNamespaces();

static SerializeHelper()
{
//去掉 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
Namespaces.Add(string.Empty, string.Empty);
}

public static string SerializeXml<T>(T obj)
{
XmlSerializer serializer = new XmlSerializer(typeof(T));
using (MemoryStream stream = new MemoryStream())
{
serializer.Serialize(stream, obj, Namespaces);
return Encoding.UTF8.GetString(stream.ToArray());
}
}

public static T DeserializeXml<T>(string obj)
{
XmlSerializer serializer = new XmlSerializer(typeof(T));
using (StringReader reader = new StringReader(obj))
{
return (T)serializer.Deserialize(reader);
}
}
}

SQL與XML，XQuery結合起來，功能會很強大的。

② sql存儲過程處理數組參數

declare@strvarchar(500)
declare@insertvarchar(8000)
select@insert='insertintoyouTablevalues'
select@str='str1,1,1,1,1|str2,2,2,2,2|str3,3,3,3,5|str4,4,4,45,4'
select@str=@insert+'('''+@str+''')'
select@str=REPLACE(@str,',',''',''')
select@str=REPLACE(@str,'|',''')'+CHAR(13)+@insert+'(''')
print@str
--exec(@str)動態執行下生成的語句就好了

--print結果如下：
insertintoyouTablevalues('str1','1','1','1','1')
insertintoyouTablevalues('str2','2','2','2','2')
insertintoyouTablevalues('str3','3','3','3','5')
insertintoyouTablevalues('str4','4','4','45','4')

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

數組不能傳遞，變通的解決辦法是有的
思路如下：
1、將數組轉換為字元串格式（例如：a,b,c,d）
2、在資料庫內創建字元串分割為行的表值函數
3、若是需要將一個二維數組傳遞的話，需要將每列數據都格式化為1的方式，然後再用2的方式轉換為行，再將轉換後的數據組合為一個表
4、你就可以直接進行插入、編輯、刪除或查詢操作了。

④ sqlserver2008存儲過程的參數有數組類型嗎

您好,沒有數組類型的.所有的參數類型都是systypes表裡面的.

⑤ 【SQL】存儲過程中如何定義數組

存儲過程里定義不了數組。如果是sqlserver,那麼你可以用表變數，游標來實現你的功能。

如果是sqlserver2005以上的版本，可以做clr存儲過程，那裡面是可以用數組的。

⑥ 【SQL】存儲過程中如何定義數組

存儲過程
里定義不了數組。如果是
sqlserver
,那麼你可以用表變數，游標來實現你的功能。
如果是
sqlserver2005
以上的版本，可以做clr存儲過程，那裡面是可以用數組的。

⑦ sql存儲過程數組參數超難問題

--方法一,用字元串替換和拼接,構造成insert
into
表
select
的樣子,然後執行
--方法二,用字元串拆分
declare
@a
varchar(800)='str1,0,0,0|str2,0,0,0|str3,0,0,0'
set
@a='insert
into
t
select
'''+@a
set
@a=replace(replace(@a,'|','''
union
all
select
'''),',',''',''')+''''
exec(@a)
/*
insert
into
t
select
'str1','0','0','0'
union
all
select
'str2','0','0','0'
union
all
select
'str3','0','0','0'
*/

⑧ 如何給存儲過程，傳一個數組參數

這個是我自己寫的一個例子，你看看：在命令窗口執行以下語句，創建自定義類型NESTEDARRAY。;在存儲過程中使用自定義類型NESTEDARRAY。PROCEDUREGET_ARR_RESULT(INPUTARRAYINNESTEDARRAY,AROUTNESTEDARRAY)ISBEGINAR:=NESTEDARRAY();FORIIN1..INPUTARRAY.COUNTLOOPAR.EXTEND;AR(I):=I||INPUTARRAY(I);ENDLOOP;ENDGET_ARR_RESULT;java代碼：importjava.sql.Connection;importjava.sql.SQLException;importoracle.jdbc.OracleCallableStatement;importoracle.jdbc.OracleTypes;importoracle.sql.ARRAY;importoracle.sql.ArrayDescriptor;importoracle.sql.Datum;/***Java獲取Oracle存儲過程返回自定義類型*@authorluckystar**/{/***@paramargs*/publicstaticvoidmain(String[]args){Connectioncon=null;OracleCallableStatementocs=null;Stringsql="{calltest.GET_ARR_RESULT(?,?)}";try{con=DBUtil.dbUtil.getConnection();ocs=(OracleCallableStatement)con.prepareCall(sql);String[]params={「10001」,」10003」};ArrayDescriptorarrayDesc=ArrayDescriptor.createDescriptor("NESTEDARRAY",con);ARRAYinputArray=newARRAY(arrayDesc,con,params);ocs.setARRAY(1,inputArray);ocs.registerOutParameter(2,OracleTypes.ARRAY,"NESTEDARRAY");ocs.execute();ARRAYarray=ocs.getARRAY(2);Datum[]datum=array.getOracleArray();for(inti=0;i

⑨ sql 存儲過程 傳入兩組數組參數

declare@nnvarchar(500)
declare@mnvarchar(500)
set@n='1,2,3,4,5'
set@m='a,b,c,d,e'

createtable#tb(nVARCHAR(500),mVARCHAR(500))
while(1=1)
begin
if(Charindex(',',@n)=0andCharindex(',',@m)=0)
begin
insertinto#tbvalues(@n,@m)
break
end
insertinto#tbvalues(Substring(@n,1,Charindex(',',@n)-1),Substring(@m,1,Charindex(',',@m)-1))
set@n=Right(@n,Len(@n)-Charindex(',',@n))
set@m=Right(@m,Len(@m)-Charindex(',',@m))
end

select*from#tb

droptable#tb

ps：要做好校驗！