Array or Generic list pass to SQL Server stored procedure to get result of SELECT query

I many time see questions in few different forums that how can we pass Array or Generic list to SQL Server to get result of SELECT statement? Well this obvious question comes to C# or any front-end developer but unfortunately there is no concept of array or list in SQL Server. SQL Server works on set based but we can have work around of this situation. You can pass value separated by comma or any other separator and use it in your WHERE condition. I have written one user define function to SPLIT value which are separated by provided separator. You can use that function here. Click here to look at my user define function to separate value.

We can use that function in our stored procedure. Let us have a look at small code snippet about how to use that?

--create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

--insert records

INSERT INTO emps

SELECT 'RITESH','MIS','ECHEM' UNION ALL

SELECT 'Rajan','acct','mar' UNION ALL

SELECT 'Bihag','MIS','ct'

GO

--use split function in SP on comma separated value

CREATE PROC SearchName

@Value varchar(max),

@separator varchar(1)

as

select * from emps where name in (select data from dbo.SplitData(@Value,@separator))

GO

--let us get comma separated value and call our SP "SearchName"

EXEC SearchName 'Ritesh,Rajan,ravi,ram',','

--you will get only two records from emps table,

--third record will be eliminated as name 'Bihag' was not

--included in above comma separated value

Happy SQL

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com