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

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

SPLIT users define function in SQL Server 2005:

We may need to split the string with some separator in SQL Server. To cater this need, I have one function which I am using since very long time and felt to share it with you. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Purpose: To convert a given string to proper case

Tested on: SQL Server 2005

Date Created:March-19-2007

Examples:

To return splitted value

select * from dbo.splitData(‘ritesh, a, shah’,’,’)

it will return three row as three word separated by comma will be splitted

*************************************************************************************************/

CREATE FUNCTION [dbo].[SplitData]

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah