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

Wednesday, September 16, 2009 |

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

1 comments:

Kalpesh Prajapati said...

Very helpfull