CODEWITHSUNDEEP
sql-server-2008stored-procedures
Deep Patel
Deep Patel

Reputation: 45

Stored Procedure for multiple value with single parameter in SQL Server

I have this form in C# with a listbox where I selected 4 items. Now I want to make single stored procedure using which I can find data from single table for all this selected item with single parameter.

As I am a beginner when it comes to SQL Server, I completely don't know this type of procedure Thanks, but this is not my question's answer I want a Single Stored Procedure for all Items which are selected in ListBox

Create Procedure procedureName
(
 @ItemName varchar(50),
)
AS
BEGIN
(
Select * from item_master where item_name = @ItemName
)
END

by this Query i can find data for one ItemName, but i want for all selected Items in Listbox, even I don't know the C# code also, so plz help me....

Upvotes: 0

Views: 3534

Answers (1)

Late Starter
Late Starter

Reputation: 1109

This is a very simple example that does what you want. You would not want to use hard-coded connection strings, especially in-line, and you would want error-handling, but I am going for as much clarity as possible. You would also probably want to make the column length greater than 50 characters, but I made it match your column definition.

Also, I would recommend a generic approach, passing keys (column names) and values, so as to be able to use it for any sort of criteria, but you asked that I keep it to exactly what you require, so I trimmed it down to the essential.

This example returns all the Employees with FirstName matching any in the list passed to the stored procedure (as a user-defined table type).

First, create a user-defined table type (to hold the values you want to pass to the stored procedure) in your SQL Server database as follows:

CREATE TYPE [dbo].[FilterValues] AS TABLE(
    [Value] [varchar](50) NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Value] ASC
    )
)

The stored procedure to return the Employees looks as follows (note that it has the user-defined table type as the type of the single parameter passed in):

CREATE PROCEDURE [dbo].[GetEmployees] (
    @FirstNameFilterValues dbo.FilterValues READONLY
)
AS
BEGIN
    SELECT * FROM Employees 
    INNER JOIN @FirstNameFilterValues fv ON fv.Value = Employees.FirstName; 
END

That's the SQL Server side done. To call it from C#, you can create a DataTable with a single column matching the column name and populate it with the values you want. In this simple example, I populate it with two names, but it could be as many as you want. 

var filterValuesDataTable = new DataTable();
filterValuesDataTable.Columns.Add(new DataColumn("Value", typeof(string)) { AllowDBNull = false });
filterValuesDataTable.Rows.Add("Frodo");
filterValuesDataTable.Rows.Add("Sam");

using (var connection = new SqlConnection("server=.;Initial Catalog=Test;Integrated Security=True;"))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "GetEmployees";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@FirstNameFilterValues", filterValuesDataTable);
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine("{0} {1}", reader["FirstName"], reader["LastName"]);
            }
            reader.Close();
        }                    
    }
    connection.Close();
}

Upvotes: 2

Related Questions