Passing some sort of Data Structure to a Stored Procedure

Hi all,
Is it possible to pass some sort of array to a stored procedure using
ADO.net.
In particular, I have a list of usernames that I need to pass to the
procedure and then have the procedure loop through that array and perform an
update action on the database.
The only alternative I can think of is to call a stored procedure over and
over again. I'd rather pass th usernames in bulk. Can anyone suggest how to
do this?
Thanks all
Kindest Regards
SimonSimon
This is one approach
CREATE PROCEDURE sparray_method
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC sparray_method
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#YWnhn4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to pass some sort of array to a stored procedure using
> ADO.net.
> In particular, I have a list of usernames that I need to pass to the
> procedure and then have the procedure loop through that array and perform
an
> update action on the database.
> The only alternative I can think of is to call a stored procedure over and
> over again. I'd rather pass th usernames in bulk. Can anyone suggest how
to
> do this?
> Thanks all
> Kindest Regards
> Simon
>|||If the list is short, perhaps Uri's method would be faster...
You could also parse the list using SQL string commands in a loop and do the
updates
You could also store the names in a #temp table and have the SP join to the
#temp table to choose which rows would be updated...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23YWnhn4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to pass some sort of array to a stored procedure using
> ADO.net.
> In particular, I have a list of usernames that I need to pass to the
> procedure and then have the procedure loop through that array and perform
an
> update action on the database.
> The only alternative I can think of is to call a stored procedure over and
> over again. I'd rather pass th usernames in bulk. Can anyone suggest how
to
> do this?
> Thanks all
> Kindest Regards
> Simon
>|||Thank you all
Simon|||YOU MIGHT JUST TRY USING THE "adArray" type declaration for the type of data
being passed. I would guess that you would create a dimensioned array
Dim A As Variant
A = Array(30)
A(1) = "JOHN"
A(1) = "JOHN2"
With cmd_Users_Update
.ActiveConnection = Users_DB_Connection
.CommandType = adCmdStoredProc
.CommandText = "dp_process_users_array"
.Parameters.Append .CreateParameter("@.users_array", _
adArray, adParamInput)
.parameters("@.users_array").Value = A
End With
and pass that array to the parameter of the command object.
Dan Kirk