Validating data is one of the most important sections in data-driven applications. It's a critical task to control and validate data that are inserting or updating. Most of developers are using constrains (Default, Check, Unique) in order to do so; most of them are using Check constrains in order to validate fields.
Sometimes, a developer needs to use a validation frequently. For example, imaging you're going to validate email address in SQL Sever. In order to do so, you'll need to write a complex regular expression as a check constraint. But there is one better way; you can use Rules to store a validation expression and then use it where ever you want.

Creating a rule is simple:

CREATE RULE EmailValidator
AS
begin

@value like '%@%.[a-z][a-z].[a-z][a-z]';

end

Once you create a rule, you can use frequently. In order to do so, you need to make use of sp_bindrule system stored-procedure.

The structure of sp_bindrule property is something like this:

sp_bindrule [ @rulename = ] 'rule' , 
[ @objname = ] 'object_name' 
[ , [ @futureonly = ] 'futureonly_flag' ]

And here’s an example. Note that today is the name of our rule:

USE master;
GO
EXEC sp_bindrule 'today', 'HumanResources.Employee.HireDate'

For more information about sp_bindrule you can out its MSDN article: http://msdn.microsoft.com/en-us/library/ms176063.aspx

During my Pocket PC project development, I needed to convert Gregorian date to Persian date. It’s easy to do such a thing in Windows or Web applications using System.Globalization namespace (PersianCalendar class) but this feature is not available when developing a Pocket PC application. I’ve tried a lot of things such as web services, windows services, etc but none of them is as good as converting it in your database and fortunately my database is Microsoft SQL Server 2005. So I decided to use CLR integration and create an UDF (User-Defined Function) in order to do so. In this post I wanted to show you how you can use CLR to create a function in .NET environment and execute it in SQL Server 2005-2008 environments.

The first thing you need is to create a SQL Server project which is available in Database node in new Project window:

Note that I used .NET Framework 2 because I wanted to create a CLR function in SQL Server 2005. If you’re using SQL Server 2008, there’s no problem to use .NET Framework 3.5 (SP1).

After you create a SQL Server project you can add any object that is common in SQL Server to your project by right click on the project and point to add; so you add a UDF, SP, etc:

As I mentioned before, we want to create an application which converts DateTime to another culture so I need to create a UDF. I select User-Defined Function and name it “PersianDateConvertor”. As you can see, Visual Studio will create a class named UserDefinedFunctions and add a method with the name you just specified:

using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{    
[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString PersianDateConvertor()    
{    
}
};

Now you need to convert a DateTime to a Persian DateTime like before using System.Globalization.PersianCalendar class. So our class will be something like this:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString PersianDateConvertor(DateTime dt)
{
    PersianCalendar p = new PersianCalendar();
    return String.Format("{0}/{1}/{2}",
         p.GetYear(dt).ToString(),
        p.GetMonth(dt).ToString(),
        p.GetDayOfMonth(dt).ToString());
}

By the way, As you can see our method is a static method and it returns a SqlString. You can change the return type if you want.

You’ve created you CLR library! Now it’s time to use it in Microsoft SQL Server. By default, you cannot use CLR libraries in SQL Server unless you enable it. In order to enable CLR integration, you need to make use of sp_configure system stored-procedure:

EXECUTE sp_configure 'clr enabled' , '1'

Note: In order to enable CLR, you need administrative privileges.

In order to make use of an external assembly in SQL Server, you have to create an assembly like following:

CREATE ASSEMBLY DateConvertorFROM 'D:\MMR.CRL.DateConvertor.dll'WITH PERMISSION_SET = SAFE;GO

After you’ve created your assembly, you need to use it in an UDF, SP, etc:

CREATE FUNCTION dbo.GetPersianDate(@date datetime)
RETURNS nvarchar(max)AS EXTERNAL NAME DateConvertor.UserDefinedFunctions.PersianDateConvertor;
GO

You’re DONE! now you can use your UDF and the result will be something like this:

SELECT dbo.GetPersianDate(GetDate())

Result:
-------------------------------------------------------------
1387/12/2
(1 row(s) affected)

A while ago, when I was working in a company with my friend Omid Mafakher, he developed a table function which works just link split function in other languages such as VB.NET, C#, C++, etc.

After I get permission from Omid, I decided to publish it as a blog post. Here's the function:

-- This stored procedure splits a string value with the specified delimiter.
-- Copyright 2007 Omid Mafakher.
Create Function [dbo].[Split] (@String nvarchar(MAX), @Delimiter char(1))
Returns @Results Table (Items nvarchar(MAX))
As
Begin
    Declare @Index int
    Declare @Slice nvarchar(4000) 

    Set @Index = 1
    If @String Is NULL Return 

    While @Index != 0 Begin
        Select @Index = CharIndex(@Delimiter, @String)
        If (@Index != 0) Begin
            Select @Slice = left(@String, @Index - 1)
        End else Begin
            Select @Slice = @String
        End
        Insert into @Results(Items) Values (@Slice)
        Select @String = right(@String, Len(@String) - @Index) 

        If Len(@String) = 0 break
    End
    Return
End

SplitFunction.sql (1.38 kb)