How can I Pass an array into a Stored Procedure from Visual Basic?

Hi
How can I Pass an array into a MS SQL Server Stored Procedure from Visual
Basic?
Thanks
Ian
Convert it into a string
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>
|||Define what you mean by an array and what you want to do with it in the
stored procedure for better answers.
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>
|||There are no array type in t-SQL. You will have to use other workarounds
like changing your VB array to a delimited string & pass as a single
parameter to the procedure, or build an XML string etc. As far as dealing
with such parameters & parsing them within a stored procedure, you can get
some ideas from: http://www.sommarskog.se/arrays-in-sql.html
Anith
|||CREATE Procedure sp_test_array
@.IDList Varchar(8000)
AS
-- Notes: @.IDList must be a comma delimited list of id's
SET NOCOUNT ON
DECLARE @.Pos1 Int, @.Pos2 Int, @.id Int
-- Input value - bracket with commas
SET @.IDList = ',' + @.IDList + ','
SET @.Pos1 = 1
WHILE @.Pos1 < Len(@.IDList)
BEGIN
SET @.Pos2 = Charindex(',' , @.IDList , @.Pos1 + 1)
SET @.id = Convert(Int, Substring(@.IDList, @.Pos1 + 1, @.Pos2 - @.Pos1 - 1))
PRINT @.id
SET @.Pos1 = @.Pos2
END