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 Value with System.Text.RegularExpressions.Regex.Split in C#

There are many ways to split the value in C# but the shortest yet efficient way is to use Split method of RegularExpression.Regex class. Let us see how it works. I will show you this example in Windows Application using C# but you can use same concept in ASP.NET with C#. If you want to use it in VB.NET than minor syntax change should be made in the function but the concept, class and method will remain same.

Let us one new Windows Application with C# and open code windows of FORM after creating one Label in windows FORM.

private void Form1_Load(object sender, EventArgs e)

{

label1.Text = “”;

string strTest = “1!800!200,19!1,0!2!!114”;

string[] strSplitedValue = returnSplittedArray(strTest, “!”);

foreach (string str in strSplitedValue)

{

if (str.Length > 0)

{

label1.Text += str;

label1.Text += “\n”;

}

}

}

public static string[] returnSplittedArray(string fullString,string separator)

{

string[] splitArray;

splitArray = System.Text.RegularExpressions.Regex.Split(fullString, System.Text.RegularExpressions.Regex.Escape(separator));

return splitArray;

}

That’s it, you are done, returnSplittedArray is very small and handy function for anyone.

Happy Programming!!

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 of
http://www.SQLHub.com