SQL-equivalent to NVL2

I have been using T-SQL for as long as I can remember and one of the things I never found was an equivalent to Oracle's NVL2. Well, this time around I did not want to use a CASE statement or even the more involved COALESCE. I just needed a plain function that would evaluate if the given expression is null return this otherwise return this.

The following T-SQL function will provide the equivalent to NVL2, or at least something close enough. The trick is to use the sql_variant data type.

IF EXISTS (SELECT id
             FROM dbo.sysobjects
            WHERE id = OBJECT_ID(N'[dbo].[isnull2]')
              AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION [dbo].[isnull2]
END
GO
CREATE FUNCTION [dbo].[isnull2]
(
    @expression sql_variant,
    @return_if_null_value sql_variant,
    @return_if_not_null_value sql_variant
)
RETURNS sql_variant
AS
BEGIN
    DECLARE @return sql_variant;
    IF (@expression IS NULL)
        SET @return = @return_if_null_value
    ELSE
        SET @return = @return_if_not_null_value
    RETURN @return
END

Please note that you will need to ensure you cast either the parameters you pass or the result to the desired type. This is specially true if you are calling the logic in a stored procedure or embedded query that is executed from a .NET or similar application via something like ADO.NET.

Read full post...

How to get the SmartAutoCompleteExtender to work with ASP.NET AJAX 1.0 RTM

If you are using the SmartAutoCompleteExtender by Infinities Loop with pre-release versions of Atlas, and you just upgraded to the new ASP.NET AJAX 1.0 RTM release, you will have to make some minor changes to the SmartAutoCompleteExtender code for it to work again.

1. In the project where the SmartAutoCompleteExtender resides, add a reference to the AjaxControlToolkit.dll assembly. This is now required since the AutoCompleteExtender, on which the SmartAutoCompleteExtender is based, is now part of the AJAX Control Toolkit project. Download it and add a reference to its assembly in your own project(s). (Thank you Dave for reminding me!)

2. In SmartAutoCompleteBehavior.js, go to the end of the file and replace the last line with the following. Note the namespace of the base AutoCompleteBehavior has changed.

i88.UI.SmartAutoCompleteBehavior.registerClass(
    'i88.UI.SmartAutoCompleteBehavior',
    AjaxControlToolkit.AutoCompleteBehavior);

3. In SmartAutoCompleteExtender.cs, add a using reference to the AjaxControlToolkit namespace.

using AjaxControlToolkit;

4. In SmartAutoCompleteExtender.cs, go to the method GetScriptReferences() and change the first reference line with the following. Note the parameters to the call need to be changed.

references.Add(new ScriptReference(
    "AjaxControlToolkit.AutoComplete.AutoCompleteBehavior.js",
    "AjaxControlToolkit"));

5. In SmartAutoCompleteExtender.cs, go to the method GetScriptDescriptors() and change the line containing serviceURL to servicePath.

if (!string.IsNullOrEmpty(ServicePath))
{
    sbd.AddProperty("servicePath", ResolveClientUrl(ServicePath));
}

6. In SmartAutoCompleteExtender.cs, go to the method GetScriptDescriptors() and change the line containing completionElementID to completionList.

if (!string.IsNullOrEmpty(CompletionListElementID))
{
    sbd.AddElementProperty("completionList", CompletionListElementID);
}

If you made the necessary reference changes to the new assemblies and updated your Web.config you should be up and running in no time.

Download sample VS2005 SP1 Web Application solution

Read full post...