Sending multiple parameters from Asp.Net to my stored procedure in SQL Server

Question

I have a list box in Asp.Net from where the user selects one or multiple parameters and send it to a stored procedure. The selected of number of parameters depends completely on the user so I don't know how many parameters the user is going to choose from the list box. I also want to retrieve data back from the table with those parameters when I click on the Submit button and display on a gridview. The issue I am having is I can send one parameter and retrieve data back from my stored procedure but I really don't know how to send multiple parameters from the list box to my stored procedure.

Below is the code for single parameter in Asp.Net

protected void Button_Click(object sender, EventArgs e)
{
    string s = "Submit";
    SqlCommand cmd = new SqlCommand(s, con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = lbCT.SelectedItem.Value;

    con.Open();

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    gvDS.DataSource = ds; 
    gvDS.DataBind();

    con.Close();
}

Below is my stored procedure in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [Submit]
    @Name VARCHAR(12)
AS
BEGIN
    SELECT *
    FROM Employee 
    WHERE Name = @Name
END

Look into table valued parameters. Sounds like that is exactly what you are looking for. — Sean Lange, Jun 28 '18 at 21:04
Other tips: `SqlCommand` and `SqlDataAdapter` are both `IDisposable` so should be in `using` blocks. And so is the `SqlConnection`, so you may want to consider changing that to being a local variable as well (if you do that, you won't need the `con.Close()`). — Richardissimo, Jun 28 '18 at 21:52
Possible duplicate of [Pass Dictionary to Stored Procedure T-SQL](https://stackoverflow.com/questions/19957132/pass-dictionarystring-int-to-stored-procedure-t-sql) — Nick.Mc, Jun 29 '18 at 04:35

Caner LENGER · Answer 1 · 2018-07-02T23:11:08.373

You are sending just parameter and using equals "=" operator. Instead of this, you should send all selected items and split your parameter by delimiter.

Please follow these steps:

1.Create a new sql function for split name/names

CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX))
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

2.Update your procedure

WHERE Name in (Select dbo.splitstring (@Names))

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [Submit]
    @Names VARCHAR(MAX)
AS
BEGIN
    SELECT *
    FROM Employee 
    WHERE Name in (Select dbo.splitstring (@Names))
END

3.Update your Codebehind parameter

cmd.Parameters.Add("@Names", SqlDbType.VarChar).Value = join all selected items with ','

protected void Button_Click(object sender, EventArgs e)
{
    string s = "Submit";
    SqlCommand cmd = new SqlCommand(s, con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@Names", SqlDbType.VarChar).Value = /*join all selected items with ','*/

    con.Open();

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    gvDS.DataSource = ds; 
    gvDS.DataBind();

    con.Close();
}

Sending multiple parameters from Asp.Net to my stored procedure in SQL Server

1 Answers1