Using a comma delimited string with id's as input parameter for a SQL query
I just recently found out about a custom Split function for SQL Server 2000/2005 while I was reading this article: Designing Reports with SQL Server Reporting Services 2005. It mentioned a custom Split function made in T-SQL that could take in a delimited string with id's.
Today I had the need of such a functionality in my current project and luckily remembered where I saw it in the first place. If you check several checkboxes and press the button you get, for those selected records, to see everything that's in the database for those.
Split function:
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = N'Split'
)
DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
GO
This is how to call the split function using a stored procedure:
CREATE PROCEDURE USP_RetrieveInformationForSelectedEmployees
@p_selectedEmployees NVARCHAR(50)
AS
BEGIN SELECT * FROM Employees
WHERE EmployeeID in (SELECT Item FROM split(@p_selectedEmployees, ','))
END
Lastly, this is how it's all put together:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Button1_Click(object sender, EventArgs e)
{
RetrieveInformationForSelectedEmployees();
}
private void RetrieveInformationForSelectedEmployees()
{
StringBuilder sb = new StringBuilder();
// First loop through the GridView and see which
// employees were selected. I use the StringBuilder
// since the list could be a very long list.
foreach (GridViewRow row in GridView1.Rows)
{
if (((CheckBox)row.FindControl("CheckBox1")).Checked)
{
sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
}
}
using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
["NorthwindConnectionString"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
GridViewResult.DataSource = ds;
GridViewResult.DataBind();
}
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="Title" HeaderText="Title"
SortExpression="Title" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title]
FROM [Employees] ORDER BY [LastName], [FirstName]">
</asp:SqlDataSource>
<asp:Button ID="Button1" runat="server" Text="Retrieve data" OnClick="Button1_Click" />
<p>
<asp:GridView runat="server" ID="GridViewResult" />
</p>
</div>
</form>
</body>
</html>