Passing an array to a stored procedure

Its very much often with the programmer that they need to pass an array or list as an argument or parameter in a stored procedure. I find this kind of scenario when the query consists of an 'IN' clause.
Lets say we have a generic list of string which we need to pass in a stored procedure which i need to use into the 'IN' clause, then what should we do. Unfortunately, I did not found any built-in support for arrays in SQL Server's T-SQL.
To overcome this problem I found several alternatives but I am sharing here which seems to me the most easier one.
We can use a comma separated value straing to pass as an array. Here we are passing a single simple string but when it is executed it acts like an comma separated value in the query string.

The sample create table and stored procedure are as below :

CREATE TABLE tbl_info
(
 
id varchar(2),
[name] varchar(100)




)

insert into tbl_info
select 'a1', 'rasel'
union all
select 'a2', 'rajib'
union all
select 'a3', 'Mujahid'
union all
select 'a4', 'Rokon'
union all
select 'a5', 'Sarwar'
union all
select 'a6', 'Reen'
 

CREATE PROC uspGetIdName

    @ListName varchar(100)

AS
BEGIN
   
    DECLARE @SQL varchar(6000)

    SET @SQL =
    'SELECT * from tbl_info where [name] in (' + @ListName + ')'

    EXEC(@SQL)   
END
GO



For example in C#.net we are having a LIST  lst_name and the member of the list are going to be used into the 'IN' clause of a query. So, create a string consisting of all members name in the list,
String param_string = "";

foreach(String s in lst_name)
{
  param_string = param_string + s;
}

Now pass the param_string into the stored procedure. If the lst_name consist of two names "rasel" and "rajib", then if we pass the param_string using the above procedure, the execute command will be similar to below :
EXEC uspGetIdName ' ''rasel'', ''rajib''  '

And you will get the desired result set.

As I said there are several other alternatives to overcome this problem, there are some limitaiton also to this technique. This is only possbile when we use dynamic query. But there are some drawback of using dynamic query generation and execution.

Insert Mutiple rows in a table from a single INSERT command.

Sometimes we need to insert numbers of data(multiple) in a single table. Usually we use transaction for executing multiple sql-commands. But if we want to insert data using a single command then we can use the UNION operator to get the multiple rows a view and then insert it into our desired table.
Here I am giving a simple example to insert multiple rows form a single INSERT command.

Create table script :
CREATE TABLE tbl_test
(
id varchar(2),
email varchar(100)

)

/*a very simple table having only two column*/

Now insert multiple rows in this table using a single INSERT command :

insert into tbl_test
select 'a1', 'ras.ewu@gmail.com'
union all
select 'a2', 'Kiser01360@gmail.com'
union all
select 'a3', 'rasel_21@hotmail.com'
union all
select 'a4', 'mr_islam_2002@yahoo.com'
union all
select 'a5', 'bdsarwar@gmail.com'
union all
select 'a6', 'raj.aryans@gmail.com'


Now If the select all command executed the result will show all the 6 rows inserted in the table.

SELECT * from tbl_test

The output screen shot image is attached.

Now we can make this script generate in the upper layer, i.e: we can make the script as a string in our C# page and pas this as a parameter into stored procedure and then execute that string and easily solve our purpose.

Insert Mutiple rows in a table from a single INSERT command.

Sometimes we need to insert numbers of data(multiple) in a single table. Usually we use transaction for executing multiple sql-commands. But if we want to insert data using a single command then we can use the UNION operator to get the multiple rows a view and then insert it into our desired table.
Here I am giving a simple example to insert multiple rows form a single INSERT command.

Create table script :
CREATE TABLE tbl_test
(
id varchar(2),
email varchar(100)

)

/*a very simple table having only two column*/

Now insert multiple rows in this table using a single INSERT command :

insert into tbl_test
select 'a1', 'ras.ewu@gmail.com'
union all
select 'a2', 'Kiser01360@gmail.com'
union all
select 'a3', 'rasel_21@hotmail.com'
union all
select 'a4', 'mr_islam_2002@yahoo.com'
union all
select 'a5', 'bdsarwar@gmail.com'
union all
select 'a6', 'raj.aryans@gmail.com'


Now If the select all command executed the result will show all the 6 rows inserted in the table.

SELECT * from tbl_test

The output screen shot image is attached.

Now we can make this script generate in the upper layer, i.e: we can make the script as a string in our C# page and pas this as a parameter into stored procedure and then execute that string and easily solve our purpose.

Insert Mutiple rows in a table from a single INSERT command.

Sometimes we need to insert numbers of data(multiple) in a single table. Usually we use transaction for executing multiple sql-commands. But if we want to insert data using a single command then we can use the UNION operator to get the multiple rows a view and then insert it into our desired table.
Here I am giving a simple example to insert multiple rows form a single INSERT command.

Create table script :
CREATE TABLE tbl_test
(
id varchar(2),
email varchar(100)

)

/*a very simple table having only two column*/

Now insert multiple rows in this table using a single INSERT command :

insert into tbl_test
select 'a1', 'ras.ewu@gmail.com'
union all
select 'a2', 'Kiser01360@gmail.com'
union all
select 'a3', 'rasel_21@hotmail.com'
union all
select 'a4', 'mr_islam_2002@yahoo.com'
union all
select 'a5', 'bdsarwar@gmail.com'
union all
select 'a6', 'raj.aryans@gmail.com'


Now If the select all command executed the result will show all the 6 rows inserted in the table.

SELECT * from tbl_test

The output screen shot image is attached.

Now we can make this script generate in the upper layer, i.e: we can make the script as a string in our C# page and pas this as a parameter into stored procedure and then execute that string and easily solve our purpose.

Autocomplete textbox using web-service and jquery plugin

It is useful now using auto-compete rather than loading all the data in a drop down list. There are several way to this. Microsoft asp.net providing AutoCompleteExtender ajaxtoolkit. But here i have used the popular Jquery autocomplete plugin which uses web-service to load the data once and then with the search string from the text box we actually show data from the previously loaded list.

Here are the steps below :

First call the web method from client side to laod the data.

var lstPersons;//Get data, fill into array
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
url: "SearchService.asmx/Search",
data: "{}",
success: function(res) {
//Fill the array with the recieved data
lstPersons = res.d;
}
})

*Notice you will get the return list array in a res.d by default.

Then from the html control focus event we query the result from the lstPerson.

$("#txt_serach").focus(function() {
$("#txt_serach").autocomplete(lstPersons, {
minChars: 0,
width: 300,
matchContains: true,
autoFill: false,
formatItem: function(row, i, max) {
return i + "/" + max + ": \"" + row.PreName + ' ' + row.Name + "\" [" + row.Country + "]";
//return row.DocTitle;
},
formatMatch: function(row, i, max) {
return row.PreName + ' ' + row.Name; //row.Name;
},
formatResult: function(row) {
//return row.DocTitle;
return row.PreName + ' ' + row.Name;
}
});
});



The output will look as below :



To implement above auto complete I have used here Jquery Autocomplete plugin
* Autocomplete - jQuery plugin 1.0.2
* Copyright (c) 2007 Dylan Verheul, Dan G. Switzer, Anjesh Tuladhar, Jörn Zaefferer
* Dual licensed under the MIT and GPL licenses

and special thanks to Mr. Abdullah Kiser.

Sample code is available at
http://www.codeproject.com/KB/aspnet/Autocomplete_textbox.aspx