# Wednesday, May 02, 2007
« Silverlight on Channel9 | Main | Mashup with ASP.NET AJAX article by Alle... »

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 luckely remembered where I saw it in the first place. Since I like it I thought I would create a small example for it. What it does is retrieve, from the Northwind database, some data of the Employees table and binds it to a GridView control. If you check several checkboxes and press the button you get, for those selected emloyees, to see everything that's in the database for them.

Grab the Split function from the article or get it here:

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

All credits go the respective author of the formentioned article for making the function.

I created the following stored procedure that makes use of the Split function:

CREATE PROCEDURE USP_RetrieveInformationForSelectedEmployees
    @p_selectedEmployees    NVARCHAR(50)
AS
BEGIN
    SELECT * 
    FROM Employees
    WHERE EmployeeID in (SELECT Item FROM split(@p_selectedEmployees, ','))
END

And this is the ASP.NET webform:

 

   1:  <%@ Page Language="C#" %>
   2:  <%@ Import Namespace="System.Data.SqlClient" %>
   3:  <%@ Import Namespace="System.Web.Configuration" %>
   4:  <%@ Import Namespace="System.Data" %>
   5:   
   6:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
   7:      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   8:   
   9:  <script runat="server">
  10:   
  11:      protected void Button1_Click(object sender, EventArgs e)
  12:      {
  13:          RetrieveInformationForSelectedEmployees();
  14:      }
  15:   
  16:      private void RetrieveInformationForSelectedEmployees()
  17:      {
  18:          StringBuilder sb = new StringBuilder();
  19:   
  20:          // First loop through the GridView and see which
  21:          // employees were selected. I use the StringBuilder
  22:          // since the list could be a very long list.
  23:          foreach (GridViewRow row in GridView1.Rows)
  24:          {
  25:              if (((CheckBox)row.FindControl("CheckBox1")).Checked)
  26:              {
  27:                  sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
  28:              }
  29:          }
  30:   
  31:          using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
  32:              ["NorthwindConnectionString"].ConnectionString))
  33:          {
  34:              using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
  35:              {
  36:                  cmd.CommandType = CommandType.StoredProcedure;
  37:                  cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
  38:   
  39:                  DataSet ds = new DataSet();
  40:                  SqlDataAdapter da = new SqlDataAdapter(cmd);
  41:                  da.Fill(ds);
  42:   
  43:                  GridViewResult.DataSource = ds;
  44:                  GridViewResult.DataBind();
  45:              }
  46:          }
  47:      }
  48:      
  49:  </script>
  50:   
  51:  <html xmlns="http://www.w3.org/1999/xhtml" >
  52:  <head runat="server">
  53:      <title>Untitled Page</title>
  54:  </head>
  55:  <body>
  56:      <form id="form1" runat="server">
  57:      <div>
  58:          <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
  59:              DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
  60:              <Columns>
  61:                  <asp:TemplateField>
  62:                      <ItemTemplate>
  63:                          <asp:CheckBox ID="CheckBox1" runat="server" />
  64:                      </ItemTemplate>
  65:                  </asp:TemplateField>
  66:                  <asp:BoundField DataField="LastName" HeaderText="LastName" 
  67:                      SortExpression="LastName" />
  68:                  <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
  69:                      SortExpression="FirstName" />
  70:                  <asp:BoundField DataField="Title" HeaderText="Title" 
  71:                      SortExpression="Title" />
  72:              </Columns>
  73:          </asp:GridView>
  74:          <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  75:              ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
  76:              SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title] 
  77:              FROM [Employees] ORDER BY [LastName], [FirstName]">
  78:          </asp:SqlDataSource>
  79:          
  80:          <asp:Button ID="Button1" runat="server" Text="Retrieve data" OnClick="Button1_Click" />
  81:          <p>
  82:              <asp:GridView runat="server" ID="GridViewResult" />
  83:          </p>
  84:      </div>
  85:      </form>
  86:  </body>
  87:  </html>

In lines 23 upto 29 I loop over the rows in the GridView and see if the checkbox in the first column was checked. If it was, I append the DataKey value of it to the StringBuilder instance. On line 37 I pass the string of all the selected employee IDs. The stored procedure uses the Split function in the IN clause.

Have fun!

Grz, Kris.

kick it on DotNetKicks.com