SQL Server 2005 Table-Valued Functions and CROSS APPLY

I ran into a situation earlier today where I wanted to use a table-value function, which essentially is just a function that returns a table. But in my case I wanted to take that table and have it joined to another table to produce the desired results.

 

I had never really had to do something like that before and I quickly found out that you can't do a standard JOIN or subquery on a table-value function if you are also trying to pass it a value derived from the table you are joining on.

 

For example, this query will work:

 

select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(@RecordID) m on d.RecordID = m.RecordID

 

However, this query will not work:

 

select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(d.RecordID) m on d.RecordID = m.RecordID

 

I realize I could have accomplished what I wanted by eliminating the function all together and having a subquery to do this all, but the point wasn't to just get it working, it was to get it working and have this helper function (MyFunction) available to centralize some logic that is used in multiple places.

 

Luckily in SQL Server 2005 there is a CROSS APPLY clause that makes this trivial:

 

select            d.RecordID,
                    d.StudyID,
                    d.TrackingID
from             MyTable d
cross apply    dbo.MyFunction(d.RecordID) m

 

The APPLY clause acts like a JOIN without the ON clause and comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side if the table-valued-function returns rows.

 

In my particular example the query returned the data that I needed and I was able to keep my logic centralized.

 

These articles were helpful as I was testing and learning about the CROSS APPLY clause:

 

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply

 

As with any SQL queries, you should test performance accordingly and determine if this particular solution is the best approach or if something else works better. I experimented with a couple other solutions; however, none of them performed any better and they didn't allow me to re-use them easily, which is why I decided the CROSS APPLY was worth using.

XSL Class

On several occassions I work with XML data and sometimes binding it to one of the standard ASP.Net controls doesn't quite give you the flexibility necessary to display the data how you need to, especially when dealing with hierarchical data more than a couple of levels deep.

You could always load the XML data and parse through it and build your controls manually; however, sometimes that is hard to do for very complex hierarchies.

In some cases a better solution is XSL.

XSL is a family of recommendations for defining XML document transformation and presentation.

XSL Transformations (XSLT)
A language for transforming XML

XML Path Language (XPath)
An expression language used by XSLT to access or refer to parts of an XML document. (XPath is also used by the XML Linking specification)

XSL Formatting Objects (XSL-FO)
An XML vocabulary for specifying formatting semantics

If you don't use XSL or know much about it and are wanting to learn I highly recommend Michael Kay's XSLT 2nd Edition Programmer's Reference by WROX Books. In my opinion Michael is one of the leading experts on the subject and you'll consistently see him posting on online forums about what XSL is and the proper way to use it, etc.

Over the course of several years I've used XSL in a number of ways such as re-transforming XML data from one format to another, advanced sorting capabilities, or displaying hierarchical data in a very specific format. The powerful capabilities of this language are too large in number to begin to outline or describe in this article; however, one thing that I would like to mention is how you can actually implement an XSL transformation in your code very easily, and in a generic fashion that can be utilized throughout your projects with minimal effort.

The purpose of this class is to centralize many of the things I have come across while using XSL and have them easily accessible and configurable in a reusable class.

This class will let you transform XML data in a file or XML data passed in directly as a string. It could easily be modified to accept the XML as an XMLDocument or other format if needed.

Another interesting thing I want to point out is that the TransformXml function is overloaded to accept either the path of the XSL file or a custom XSL configuration object (XslConfiguration).

Sometimes your code might need to be as simple as passing in XML data and an XSL path and getting back the results:

Using Xsl As New Utilities.Xsl()
     News.Text = Xsl.TransformXml(Utilities.Xsl.XmlTypes.XmlData, XmlData, Server.MapPath("~/Test.xsl"))
End Using

However sometimes you have more complicated requirements, thus the overloaded version of the TransformXml function can take an XslConfiguration object as a parameter. This allows you to define a number of other things besides the XSL path, including parameters that the XSL transformation needs and an ExtensionObject if you need to execute custom functions from within the transformation.

Here's a similar example using the XslConfiguration object and only specifying an XSL file, no advanced configuration settings:

Using Xsl As New Utilities.Xsl()
     Using XslConfig As New Utilities.Xsl.XslConfiguration(Server.MapPath("~/Test.xsl"))
          HtmlData = Xsl.TransformXml(Utilities.Xsl.XmlTypes.XmlData, XmlData, XslConfig)
     End Using
End Using

And here's another example using the XslConfiguration object with some of the more advanced settings:

Using Xsl As New Utilities.Xsl()
     Using XslConfig As New Utilities.Xsl.XslConfiguration()
          'xsl file path
          XslConfig.XslFile = XslFile

          'extension object with a function that will be accessible in our transformation
          XslConfig.ExtensionNameSpace = "urn:ext"
          XslConfig.ExtensionObject = New ExtensionObject

          'parameters required in the xsl transformation
          XslConfig.SetXslParameterNames("Parameter1Name", "Parameter2Name")
          XslConfig.SetXslParameterValues("Parameter1Value", "Parameter2Value")

          'transform the data
          HtmlData = Xsl.TransformXml(Utilities.Xsl.XmlTypes.XmlData, XmlData, XslConfig)

          'check for errors
          If Xsl.ErrorDescription <> String.Empty Then
               HtmlData = xsl.ErrorDescription
          End If
     End Using
End Using

Of course for the previous code to work you to define the ExtensionObject class or whatever you chose to name your class, for example:

Private Class ExtensionObject
     Public Function IsImageAvailable(ByVal Image As String) As String
          Return File.Exists(HttpContext.Current.Server.MapPath("~/Images/" & Image))
     End Function
End Class

The actual XSL transformation code could look something like:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ext="urn:ext" version="1.0">
     <xsl:output media-type="html" omit-xml-declaration="yes"/>

     <xsl:param name="Parameter1Name" />
     <xsl:param name="Parameter2Name" />

     <xsl:template match="/">
          Parameter1Name: <xsl:value-of select="$Parameter1Name"/><br/>
          Parameter2Name: <xsl:value-of select="$Parameter2Name"/><br/>

          <xsl:apply-templates select="//Row">
               <xsl:sort select="LastName" data-type="text" order="ascending" />
               <xsl:sort select="FirstName" data-type="text" order="ascending" />
          </xsl:apply-templates>     </xsl:template>

     <xsl:template match="Row">
          <xsl:variable name="ShowImage" select="ext:IsImageAvailable(Image)"/>
         
          <xsl:choose>
               <xsl:when test="$ShowImage='True'">
                    <img src="Images/{Image}" />
               </xsl:when>
               <xsl:otherwise>
                    <img src="Images/ImageNotAvailable.jpg" />
               </xsl:otherwise>
          </xsl:choose>
     </xsl:template>
</xsl:stylesheet>

Notice how we have our xmlns:ext attribute declared in the xsl:stylesheet node. That is what ties our extension namespace back to what we specified in the code-behind:

XslConfig.ExtensionNameSpace = "urn:ext"

And we've defined our 2 parameters that we passed in.

It also demonstrates how we can call our custom function from our extension object, like so:

<xsl:variable name="ShowImage" select="ext:IsImageAvailable(Image)"/>

That's all there is to having a custom XSL class that can be reused for a lot of different scenarios!

Stored Procedure Optimization Tips

Use stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Use sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

http://www.mssqlcity.com

Enter Key Default Submit Button

Enter Key functionality is handled a little differently when it comes to ASP.NET. 

 

If there is a single button then it is straight forward because that button is the only button and thus the default button; however, if there are two or more buttons then it uses the first button as the default button.

 

ASP.NET 2.0 introduces a work around for this by simply specifying the defaultbutton property to the ID of the <asp:Button> whose event you want to fire.

 

The defaultbutton property can be specified in the <form> tag as well as in the <asp:panel> tag. The form level setting is overridden when specified at the panel level, for those controls that are inside the panel.

 

If for some reason handling this at the form level or panel level doesn't fit with how you have things setup, you can still use some simple JavaScript techniques to accomplish this.

 

Take this JavaScript function for example, which also utilizes another custom FindControl function.

 

function Submit(e, ButtonName)
{
     var KeyCode = (window.event) ? event.keyCode : e.which;

     var KeyChar = (window.event) ? String.fromCharCode(event.keyCode) : String.fromCharCode(e.which);

     if (KeyCode == 13)
     {
          var btnSubmit = FindControl(ButtonName, "input");
         

          if(btnSubmit != null)
          {
               btnSubmit.click();
          }

 

          if (window.event)
          {
               window.event.returnValue = false;
          }
          else
          {
               e.preventDefault();
          }
     }
}

 

Now for any control on your page you can specify which button you want "clicked" when the ENTER key is pressed while that control has focus like so:

 

<asp:TextBox ID="txtTest" onkeypress="Submit(event, 'btnSubmit')" runat="server" />

 

This has been tested in IE and Firefox.

MaxLength with MultiLine TextBox

We've all ran into the issue with using a TextArea control or a TextBox control with MultiLine set to True to allow the user to enter a long description or comments, etc. Specifically the problem is that the built in MaxLength attribute that we are so used to using for a standard TextBox does not work if you are using MultiLine=True.

 

There are a variety of ways to handle this. In some cases I've simply dropped in a line or two of JavaScript and added some onkeypress events to analyze the number of characters the user has typed in and when they reach the maximum to just stop them form entering anything else. That approach works fine, but it involves having to worry about making sure you copy the JavaScript to the right pages and worry about setting up your onkeypress event for each item you want to limit the length of, etc. When researching this I came across a better solution that is pretty simple, yet it's more generic and easier to reuse throughout your projects.

 

The idea is essentially just extending the functionality of the System.Web.UI.WebControls.TextBox control.

public class TextArea : System.Web.UI.WebControls.TextBox
{
     protected override void OnPreRender(EventArgs e)
     {
          if (MaxLength > 0 && TextMode == System.Web.UI.WebControls.TextBoxMode.MultiLine)
          {
               // add javascript handlers for paste and keypress
               Attributes.Add("onkeypress", "doKeypress(this);");
               Attributes.Add("onbeforepaste", "doBeforePaste(this);");
               Attributes.Add("onpaste", "doPaste(this);");

               // add attribute for access of maxlength property on client-side
               Attributes.Add("maxLength", MaxLength.ToString());

               // register client side include - only once per page
               if(!Page.ClientScript.IsClientScriptIncludeRegistered("TextArea"))
               {
                     Page.ClientScript.RegisterClientScriptInclude("TextArea", ResolveClientUrl("~/Common/TextArea.js"));
               }
          }

          base.OnPreRender(e);
     }
}

 

The main thing to recognize is how it's adding the onkeypress, onbeforepaste, and onpaste events to execute JavaScript functions on the client.

 

Another nice thing about this code is that you don't have to worry about putting your JavaScript code everywhere you want to use this functionality. You can simply keep it centralized in a .js file and place it in a folder with your other JavaScript files.

 

In our case we have the necessary JavaScript in TextArea.js, which is as follows:

 

// Keep user from entering more than maxLength characters
function doKeypress(control)
{
     maxLength = control.attributes["maxLength"].value;
     value = control.value;
     if(maxLength && value.length > maxLength-1)

     {
          event.returnValue = false;

          maxLength = parseInt(maxLength);
     }
}

 

// Cancel default behavior
function doBeforePaste(control)
{
     maxLength = control.attributes["maxLength"].value;
     if(maxLength)
     {
          event.returnValue = false;
     }
}

 

// Cancel default behavior and create a new paste routine
function doPaste(control)
{
     maxLength = control.attributes["maxLength"].value;
     value = control.value;
     if(maxLength)
     {
          event.returnValue = false;
          maxLength = parseInt(maxLength);
          var oTR = control.document.selection.createRange();
          var iInsertLength = maxLength - value.length + oTR.text.length;
          var sData = window.clipboardData.getData("Text").substr(0,iInsertLength);
          oTR.text = sData;
     }
}

 

Now to use it you can simply register the control on your page like so:

 

<%@ Register TagPrefix="WebControls" Namespace="Test.WebControls" Assembly="Test.WebControls" %>

 

Then you can use the control as follows:

 

<WebControls:TextArea ID="txtTest" TextMode="MultiLine" MaxLength="500" Rows="10" Columns="75" runat="server" />

 

The value you specify for the MaxLength attribute will determine the maximum number of characters the user will be able to enter.

 

Of course the user can disable JavaScript, so it's always a good idea to test for that accordingly or make sure to validate on the server side as well.

Changing MasterPage programatically at runtime

Using multiple master pages is becoming more handy lately. When I first started using master pages I typically had 1 or 2 of them in a project and a page would only make sense to be viewable in one of those master pages. A good example is having a "public" master page and an "admin" master page.

But on one of my projects there is a wide variety of master pages available to provide several different "experiences" based on what the user is trying to do or what the user is viewing.

There is one particular scenario where I wanted to let the user see the content in a different master page (e.g. normal view vs. print view) and I wanted to control it based on a querystring parameter, so I needed to change the master page at runtime.

Changing the master page programatically in your code-behind file is easy to do, it's just important to know that you have to do it early enough in the page's life-cycle for it to actually work.

You can change the master page in the PreInit event.

Private Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
     MasterPageFile = "~/Template2.master"
End Sub

The PreInit event is new to ASP.Net 2.0. If you try to wait to change the master page in the page's Load or Init event it will be too late!

When changing the master page dynamically, you must make sure that all master pages have the same ID for the ContentPlaceHolder controls, so that the content page's Content controls will always match them, regardless of which master page is being used.

ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso

Input Controls Highlight

Here's an easy way to provide some nifty "highlight" effects to a web form to give the user a helpful visual experience.

 

I think most of us already know the concept of a BasePage and what they are used for, and in general they more times than not are helpful to use as they can provide some very basic functionality to a variety of pages with very little effort. So without getting in to setting up the base page too much, just know that you can create a BasePage in C# or VB and have it inherit from System.Web.UI.Page. In one particular case I have multiple base pages for a variety of things done throughout the site, but the base of all base pages is typically my BasePage.vb file.

 

In BasePage.vb I override the OnLoad page event which looks like:

 

Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
     'add onfocus and onblur to controls so active control has a different appearance
     Utilities.Helpers.SetInputControlsHighlight(Page, "highlight", True)
     MyBase.OnLoad(e)
End Sub

 

In my case I call a SetInputControlsHighlight function that I have in another class in my "Utilities" project, although technically you could have the function directly in the base page if you desired. The function is as follows:

 

Public Shared Sub SetInputControlsHighlight(ByVal container As Control, ByVal ClassName As String, ByVal OnlyTextBoxes As Boolean)
     For Each ctl As Control In container.Controls
          If ((OnlyTextBoxes AndAlso TypeOf ctl Is TextBox)
              Or (Not OnlyTextBoxes
                   AndAlso (TypeOf ctl Is TextBox
                   Or TypeOf ctl Is DropDownList
                   Or TypeOf ctl Is ListBox
                   Or TypeOf ctl Is CheckBox
                   Or TypeOf ctl Is RadioButton
                   Or TypeOf ctl Is RadioButtonList
                   Or TypeOf ctl Is CheckBoxList))) Then
               Dim wctl As WebControl = CType(ctl, WebControl)
               wctl.Attributes.Add("onfocus", String.Format("this.className = '{0}';", ClassName))

               wctl.Attributes.Add("onblur", "this.className = '';")
          Else
               If (ctl.Controls.Count > 0) Then
                    SetInputControlsHighlight(ctl, ClassName, OnlyTextBoxes)
               End If
          End If
     Next
End Sub

The function is pretty simple. It's setup to take the root container as a parameter and it will apply the specified class (e.g. "highlight") to the controls in that container. There is also a parameter to specify if you want the style applyed to all input controls on the form or specifically just TextBox controls.

In my case when the user clicks on an input control on the form it shows the control with a different color so it's obvious that it has the focus, then when the focus is lost it goes back to normal, etc. It can easily be modified to apply to other situations.

The original idea for this code was based the book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso. It's one of the best books I've used throughout my career. It's published by WROX, and all of their books are usually very good (in my opinion), but this one is different than most because rather than a typical reference book, it's actually a step by step solution for building an ASP.Net 2.0 website from the ground up, from listing requirements, identifying problems, designing the database, architecting the solution, implementation, all the way to deployment, etc.

IIS 6.0 does not serve unknown MIME types

SYMPTOMS
When you request a file from an IIS 6.0 Web server, and the file has a file name extension that is not a defined MIME type on the Web server, you receive the following error message:

HTTP Error 404 - File or directory not found.

 

CAUSE
Earlier versions of IIS include a wildcard character MIME mapping, which permits IIS to serve any file regardless of its extension. IIS 6.0 does not include this wildcard character MIME mapping and does not serve any type of extension that is not defined at the MimeMap node in the IIS metabase.

 

RESOLUTION

http://support.microsoft.com/default.aspx/kb/326965

Queries to retrieve table information

If you don't have a 3rd party tool available to build a list of table definitions for you, such a field names, data-types, sizes, etc. you can query syscolumns, sysobjects, and systypes directly to get this information:

 

For example to get a list of all of the tables you could use:

 

select     o.name,

             'datecreated' = o.crdate
from       sysobjects o
where     o.xtype = 'U'
and        o.status >= 0
order by  o.name

 

Or to get the detailed table definition for a specific table you could use:

 

select       c.name,
               'type' = t.name,

               'default' = (select column_default
                             from information_schema.columns
                             where table_name=o.name
                             and column_name=c.name),
               'length' = case when t.name like '%char%' then c.prec else c.length end,
               'null' = c.isnullable,
               'identity' = sign(c.status & 128),
               'pk' = (select count(1)
                        from information_schema.table_constraints tc
                        join information_schema.key_column_usage kc
                        on tc.constraint_name=kc.constraint_name
                        where tc.constraint_type = 'PRIMARY KEY'
                        and tc.table_name=o.name
                        and kc.column_name=c.name),
               'fk' = (select count(1)
                        from information_schema.table_constraints tc
                        join information_schema.key_column_usage kc
                        on tc.constraint_name=kc.constraint_name

                        where tc.constraint_type = 'FOREIGN KEY'
                        and tc.table_name=o.name
                        and kc.column_name=c.name)
from         syscolumns c
join          sysobjects o on c.id = o.id
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and          o.name = 'MyTable'
order by    c.colorder

 

Besides the column name and data-type you can also get the default value, size, whether the field allows nulls, whether the field is an indentity value or not, whether the field is part of the primary key, and whether the field is part of a foreign key, etc.

Query to retrieve a list of SQL objects (tables or stored procedures)

Ever needed to figure out how many tables or stored procedures were going to be affected by some design change in one of your tables, like a field being renamed?

 

Aside from using a 3rd party tool, you can simply query various system tables to get this information.

 

For tables:

 

select       'TableName' = o.name,
               'ColumnName' = c.name,
               'DataType' = t.name
from        syscolumns c
join          sysobjects o on c.id = o.id
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and          c.name = 'MyFieldName'
order by    o.name

 

For stored procedures:

 

select     o.name,
             c.text
from       sysobjects o
join        syscomments c on o.id = c.id
where     o.xtype = 'P'
and        o.category = 0
and        c.text like '%MyFieldName%'
order by  o.name

 

It's easily customizable to fit your needs.

 

You can find more information on the sysobjects table here:

 

http://msdn2.microsoft.com/en-us/library/Aa260447(SQL.80).aspx

Building a Split() Function in SQL Server

So you've got a delimited list of values and you want to somehow pass those to SQL as a single parameter and use them in your query?

 

One style that would accomplish this behavior is to have your SQL statement built dynamically in one of your layers of code.

 

Another option would be to pass in the delimited list to a stored procedure and parse the string yourself with some string operations and a cursor and build a dynamic SQL statement and then use EXEC to get the results.

 

But I find myself typically wanting to use a more generic approach that has some level of reusability, hence a user defined function (UDF) to handle the split and keep the rest of the SQL statements very simple.

 

For example, let's say you have this SELECT statement:

select     ID,
             Name
from       MyTable
where     Name in ('Test1', 'Test2', 'Test3', 'Test4')

 

Nothing too exciting here. It simply returns the 4 records that we've asked for.

 

But what if we didn't know what records the user was going to ask for and we needed to make this statement more generic and parameterized.

 

Something like this would be nice; however, as you probably know, it's won't yield the results you need:

 

declare @List varchar(100)

 

set @List = 'Test1, Test2, Test3, Test4'

 

select    ID,
            Name
from      MyTable
where    Name in (@List)

 

Hence we introduce our user defined split function:

 

CREATE FUNCTION [dbo].[fnSplit]
(
     @List VARCHAR(8000),
     @Delimiter VARCHAR(1)
)
RETURNS @Table TABLE
(
     ID INT IDENTITY(1,1),
     Value VARCHAR(100)
)
AS
BEGIN
     -- loop through the list
     WHILE (CHARINDEX(@Delimiter, @List) > 0)
     BEGIN
          -- add the value to the table
          INSERT INTO @Table
               (Value)
               SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1)))

          -- remove the value from the list
          Set @List = SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + LEN(@Delimiter), LEN(@List))
     END

     -- insert remaining value from the list
     INSERT INTO @Table
          (Value)
          SELECT Value = LTRIM(RTRIM(@List))

     -- return
     RETURN
END

This function uses a simple while loop that just processes part of the delimited list at a time, removes the value, and continues processing the rest of the string until there is nothing left to process.

The final results end up being a simple table with records representing the delimited string in a tableized format.

For example:

declare @List          varchar(100)
declare @Delimiter   varchar(1)

set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','

select     *
from       dbo.fnSplit(@List, @Delimiter)

Let's modify our query from earlier to utilize our new split function and we'll see how it all ties together:

declare @List varchar(100)
declare @Delimiter varchar(1)

set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','

select     ID,
             Name
from       MyTable
where     Name in (select Value from dbo.fnSplit(@List, @Delimiter))

We now simply can pass in our delimited list and delimiter and utilize it in a simple SQL statement to get the results we needed.

Using COALESCE to Build Comma-Delimited String

This is one of my favorite little tricks to use in SQL Server programming. I don't have to use it very often, but it's definitely a nice way to build a delimited string, considering the other options available.

 

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

 

Like I said, I've only had to use this in 2 or maybe 3 places, but it definitely saved some time and definitely kept the code simple and clean!

 

Basically the old approach was to return a set of records and through some layer of code build a delimited list... Or if you were a bit more savvy you might have kept it all at the database level and used some type of cursor... But with COALESCE you can create the same results in a single SELECT statement.

 

The article shows some good examples of how to utilize this.

isql Utility

The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000.

 

http://msdn2.microsoft.com/en-us/library/Aa214007(SQL.80).aspx

 

This is handy if you don't have access to any other tools for testing SQL queries, which in most cases is probably rare, but still, it's nice to have other options available just in case.

SQL Server 2000 CAST and CONVERT

Anyone who's written SQL statements has undoubtedly had to CONVERT values from one data-type to another. I do this quite regularly, specifically with converting a varchar value to a specific date format, or converting a date from one format to another.

 

By now I've got most of the different options memorized, but this MSDN reference always was a life saver in the middle of the night when I couldn't quite remember what I was after.

 

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

 

Enjoy!

Recursive Page.FindControl for VB.Net with Generics

This makes life much easier when you're trying to get to controls that are themselves contained within other containers, eg, a TextBox inside a DataView or DataList.

Public Function FindControlRecursive(Of ItemType)(ByVal Ctrl As Object, ByVal id As String) As ItemType
     If String.Compare(Ctrl.ID, id, StringComparison.OrdinalIgnoreCase) = 0 AndAlso TypeOf Ctrl Is ItemType Then
          Return CType(Ctrl, ItemType)
     End If

 

     For Each c As Control In Ctrl.Controls
          Dim t As ItemType = FindControlRecursive(Of ItemType)(c, id)

          If t IsNot Nothing Then
               Return t
          End If
     Next

 

     Return Nothing
End Function

This example also demonstrates the use of Generics, which before .Net 2.0 was only available with cast typing and use of base objects, etc. There are several good articles online about generics, but here are a couple to get you started.

http://www.codeproject.com/csharp/Generics_Explained.asp

http://www.ondotnet.com/pub/a/dotnet/2005/06/20/generics.html