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
Advertisements

One Response to “Array or Generic list pass to SQL Server stored procedure to get result of SELECT query”


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: