SQL SERVER – Passing multiple values through one parameter in a stored procedure

In this article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure.

In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to replace the parameter with single quote and create a dynamic query and execute it.
Given below is the Example Code and Script.

Query:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Get_StockHistory]

(@Id varchar(500))

AS

Begin

set @Id = Replace(@Id, ',',''',''')

DECLARE @SqlQuery VARCHAR(MAX)

SET @ SqlQuery = 'SELECT Column1, Column2, Column3 FROM TabelName

            WHERE Column1 IN (''' + @Id + ''') ORDER BY ItemId'

EXEC (@SqlQuery)

END

Example:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Get_StockHistory]

(@ItemId varchar(500))

AS

Begin

declare @Itid

set @Itid='338,274,275,289,58,1227,322,323,324,325'

SET @Itid = REPLACE(@id,',',''',''')

DECLARE @SqlQuery VARCHAR(MAX)

SET @ SqlQuery = 'SELECT ItemId,ItemName,Price,AvailableQty FROM ItemDetail

            WHERE ItemId IN (''' + @Itid + ''') ORDER BY ItemId'

EXEC (@SqlQuery)

END

C# Count Array Elements

ArrayList

Here we get the number of elements in an ArrayList, which is a non-generic collection that stores its length in a property called Count. This is identical to the List collection, which we will see next. This property access is fast.

Program that uses ArrayList: C#

using System;

using System.Collections;

class Program

{

    static void Main()

    {

        ArrayList ar = new ArrayList();

        ar.Add("One");

        ar.Add("Two");

        // Count the elements in the ArrayList.

        int c = ar.Count;

        Console.WriteLine(c);

    }

}

Output

2 

List

Here we use List, a generic collection. You must specify its type in the sharp angle brackets <T>. These collections have superior performance and usually preferable. List has a property called Count that is fast to access.

Program that uses List: C#

using System;

using System.Collections.Generic;

using System.Linq;

class Program

{

    static void Main()

    {

        List<int> list = new List<int>();

        list.Add(1);

        list.Add(2);

        // Count with the Count property.

        int c = list.Count;

        Console.WriteLine(c);

    }

}

Output

2

Arrays

Arrays are counted differently and you need to use Length. In programs, an array's size is set differently than List or ArrayList, and it does not grow dynamically. The Length value will include all elements.

Count extension

You can use the Count() extension method when you have an IEnumerable collection or are using LINQ statements. Enumerations in C# are collections that haven't been determined exactly yet, and they can use yield and foreach keywords.

Program that uses LINQ: C#

using System;

using System.Linq;

class Program

{

    static void Main()

    {

        string[] arr = new string[]

        {

            "One",

            "Two",

            "Three"

        };

        // An enumerable collection, not in memory yet.

        var e = from s in arr

               select s;

        int c = e.Count();

        Console.WriteLine(c);

    }

}

Output

3