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'',''syscolumns'''
"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
>|||Depending on what processing you are actually doing, you
could create an ADO.NET dataset that has a datatable
holding the usernames, write the table to the SQL Server,
and then have your procedure access this table. One
advantage to this is that you may be able to perform a
joined update statement that would speed things up
drastically over looping through a list of users.
Just a thought which I hope will help.
Matthew Bando
matthew.bando@.csctgi(remove).com
>--Original Message--
>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 Varian
A = Array(30
A(1) = "JOHN
A(1) = "JOHN2
With cmd_Users_Updat
.ActiveConnection = Users_DB_Connectio
.CommandType = adCmdStoredPro
.CommandText = "dp_process_users_array
.Parameters.Append .CreateParameter("@.users_array",
adArray, adParamInput
.parameters("@.users_array").Value = End Wit
and pass that array to the parameter of the command object
Dan Kirk