Passing Array with ids to stored procedure

I want to pass and array of ids to a procedure for inserting i a relation table.

I found some examples in other posts, but had problems getting them to work.

I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.

(I´m using sql server 2000), had some problem with examples with strpos then.

Any hints ?

peace.

Create procedure ParseArray
( @.Array varchar(1000),
@.separator char(1) )
AS
-- Created by graz@.sqlteam.com
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
go

enough documentation to xplain whats going on...

hth|||Don't bother using arrays (well until Yukon) this is one area where passing XML is helpful. Construct a simple XML string <r><i x="10" /><i x="10"/></r> etc and pass that as text to the proc (if you're client object supports serialization the xml might already be there). In the proc convert the XML into a table var. Then off you go, the array is now a cell per row.

Passing @UserName into Stored Procedure

I have successfully Create a Site, Inserting Updating andDeleting information in my DB all with Stored Procedures, But I need theability to pass their username into my Stored Procedures. How and where do Icode this in my ASPX file?

My SP would be something like this

Create procedure test

@.UserNamevarchar(50)

As

Select *

From table

Where username= @.username

All of thedata is tied to the user in one way or another but I do not know what code toput in my page?

If you can get correct username in your application, why not use SqlCommand?

using(SqlConnection conn= new SqlConnection(@."Data Source=.\iori2000;Integrated Security=SSPI;Database=master"))
{
SqlCommand cmd= new SqlCommand("test",conn);
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.username", username);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);

//add your code

}

|||

I do not know that I'm following.

Here is the Code I would like to put pull the username.

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1" Style='position: relative'>
<Columns>
<asp:BoundField DataField="Task_Name" HeaderText="Task_Name" SortExpression="Task_Name" />
<asp:BoundField DataField="project_name" HeaderText="project_name" SortExpression="project_name" />
<asp:BoundField DataField="priority_desc" HeaderText="priority_desc" SortExpression="priority_desc" />
<asp:BoundField DataField="Assigned_to" HeaderText="Assigned_to" SortExpression="Assigned_to" />
<asp:BoundField DataField="status" HeaderText="status" SortExpression="status" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:WebAppConnectionString %>"
SelectCommand="task_summary_info" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</asp:Content>