passing array to stored procedure

If you are trying to pass an array to a stored procedure then you can find your self in real difficulity.
This article explains a simple method for passing an array from your C# ASP .NET application to an SQL stored procedure by using a bit of dyanmic SQL.

MSSQL Limitations and Arrays
MSSQL is a favorite database platform for C# ASP .NET developers. For the most part, MSSQL has everything you need for your application. However, one particular missing feature is support for arrays as inputs to stored procedures. At first glance, one may think passing in a series of input parameters, with a pre-defined limit (such as var1, var, var3, var4) would be a suitable workaround. However, there is an easier way to accept an array of parameters.

Simple Method to Pass Array to a Stored Procedure - SQL Server Side

Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command.

CREATE PROCEDURE [dbo].[GetData]
@MyCodes as varchar(500) = '', -- comma delimited list of codes, -: '''ABC'', ''DEF'', ''GHI'''
AS
BEGIN
DECLARE @query as nvarchar(500)

set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'

exec SP_EXECUTESQL @query,
N'@p_MyCodes varchar(500)',
@p_MyCodes = @MyCodes
END
OR
ALTER PROCEDURE [dbo].[sessions_GetSchedule]
-- Add the parameters for the stored procedure here
@confGuid varchar(500),
@sIDlist varchar(500)

AS
BEGIN

DECLARE @SQL varchar(500)
SET NOCOUNT ON;

SET @SQL = 'SELECT sID, sName, sDate, sEndDate, sTime, sEndTime FROM sessions WHERE confguid='+@confGuid+' and sID in ('+@sIDlist+')'
EXEC(@SQL)

And towards your input side

=========test page to pass values as query sreing============================

Response.Redirect("NewSchedule.aspx?CART=" +TextBox1.Text + ";" +TextBox2.Text+"&confGuid="+DropDownList1.SelectedValue);
pass as query string

====page where schedular is displayed===========AAA001;AAA002;==================
IMPORTANT

if (Request.QueryString["Cart"] != "")
{
string confGuid = Request.QueryString["confGuid"];
string sID = Request.QueryString["CART"];
string NewsID = sID.Replace(";", "','");
string FinalsID = "'"+NewsID+"'";
string FinalConfGuid= "'" + confGuid+ "'";
Label1.Text = FinalsID;
ScheduleGeneral1.DataSource = BAL.BAL.GetSession(FinalsID,FinalConfGuid);
ScheduleGeneral1.DataBind();
}

-===============BAL================='''AAA001'',''AAA002'''======
public static DataSet GetSession(string spInput,string confGuid)
{

DataSet ds = DAL.DAL.DataSetDAL1("sessions_GetSchedule", spInput,confGuid);

return ds;
}

========================DAL==============================
public static DataSet DataSetDAL1(string spName, string spInput,string congGuid)
{
DataSet _Ds = new DataSet();
try
{
SqlConnection con = GetConnection();

SqlCommand cmd = new SqlCommand("sessions_GetSchedule", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);

cmd.Parameters.Add(new SqlParameter("@sIDlist", SqlDbType.VarChar));
cmd.Parameters["@sIDlist"].Value = spInput;

cmd.Parameters.Add(new SqlParameter("@confGuid", SqlDbType.VarChar));
cmd.Parameters["@confGuid"].Value = congGuid;

cmd.CommandType = CommandType.StoredProcedure;
con.Open();

da.Fill(_Ds);
con.Close();
if (_Ds.Tables[0].Rows.Count > 0)
{
return _Ds;
}

}
catch (Exception e)
{
e.Message.ToString(); ;
}

int count = 0;
//foreach (DataRow dr in _Ds.Tables[0].Rows)
if (_Ds.Tables[0].Rows.Count>0)
{
count = count + 1;
}


return _Ds;

}