Use Temporary Table to Pass Global Variables

One limitation of stored procedures (SP) in Microsoft SQL 2005 is that there is no global variables you can define. You can pass values by parameters; however, if you want to add additional information between SPs which are already in use, it will be difficult to make change since the signature of SPs are already defined. Oracle's package is much more convenient. All the global variables can be defined in a package and you can even define private SPs. Off course, .Net assemblies developed by managed codes as C# offer much more choices.

Any way, I find a way to pass values between SPs without changing parameters: using a temporary table. A temporary table can be created in a caller SP and then all the callee SPs can access to it. I have implement this strategy in a SQL application and it works very well.

The structure is that a temporary table, for example #calculationInfo_CachedForCalc, is created in the starting SP. The table contains only one row of data, global variables. Some values are updated in the caller SP (they can be updated in any other SPs). Then the callee SPs will get the value for use. One problem is that the callee SPs may be called or executed in other cases, since they are public accessible. I have created a utility function to check if the temporary table exists or not. Here is the function:

CREATE FUNCTION [dbo].[udf_IsCachedDataAvailable](
    @p_cachedDataType int = 0)
RETURNS int
AS 
BEGIN
    DECLARE @v_ret int;
    SET @v_ret = CASE @p_cachedDataType
        WHEN 1 THEN
            WHEN OBJECT_ID( 'tempdb..#calculationInfo_CachedForCalc') IS NULL 
                THEN 1
                ELSE 0
            END
            -- ... Check for other objects by other possible parameter values
       END
    RETURN @v_ret;
END

In the start SP, or caller SP, here are some codes:

IF dbo.udf_IsCachedDataAvailable(1) = 1
BEGIN
    CREATE TABLE #calculationInfo_CachedForCalc(
        [id] [int] IDENTITY(1, 1) NOT NULL,
        [calcName] [varchar](50) NULL,
        [logFlags] [varchar](50) NULL,
        [calcCumulativeType] [varchar](10) NULL)
    INSERT INTO #calculationInfo_CachedForCalc
        ([logFlags] VALUES(@p_parameter); -- log flags passed in by SP parameter
END

-- FETCH LOOP read a row from calculation table
-- ....
    UPDATE #calculationInfo_CachedForCalc SET
        [calcName] = @v_calcNam, -- Update calculation name variable
        [calcCumulativeType] = @v_calcCumulativeType; -- Update calculation type var

    EXEC sp_CalcFormula;
-- ....

In other callee SPs, such as sp_CalcFormula(), the temporary table is accessed to get a global variable such as log flags:

IF dbo.udf_IsCachedDataAvailable(1) = 0
BEGIN
    SET @v_flags = (SELECT [logFlags] FROM #calculationInfo_CachedForCalc);
-- ....
END

Read More...

Collapse...

GridView and AJAX PopupCalendar Issue

I tried to use ASP.NET AJAX CalendarExtender control in GridView's edit template for a date field in the way just as the example show on How Do I: Configure the ASP.NET AJAX Calendar Control? That is, I added a text box binding to a field in the gridview as date, a image button next to it as a button, and a CalendarExtender control which is AJAX control. The extender control's TargetControlID is the text box, and PopupButtonID is the image control.

However, it does not work. Actually, the pop-up calendar does pop up, but it disappears right away, and the whole page is refreshed. It looks like that the lick on image button caused a call to server to send a post-back call. The example works fine, but it is too simple(a text box, a image button and a CalendarExtender on an aspx page). I am not sure why this extender does not work when it is embedded in the gridview. I think I have to find a way to prevent the post-back call so that the pop-up calendar will stay.

Read More...

Collapse...

SQL's NULL Marker and Its Propagation

NULL is a SQL's marker to indicate missing information or unknown status. It is essential part of relational database and SQL. One of NULL feature is that it can propagate the result in many cases and they do make sense.

For example, 1 + NULL is NULL, or NULL in any mathematical expression will result NULL. That's OK. However, it further propagates to SQL internal functions, like LEN and CHARINDEX. It is very difficult to argue why not the result is NULL. I have to say it will depend on your case.

Recently, I have been working on a project with Microsoft SQL 2005. I did not realize that LEN and CHARINDEX functions return NULL if its parameters are NULL. That caused problem in my stored procedures. What I need these functions is to find out if a specified string exists in an expression. The expression is a value retrieved from db table. For my case, if the value is NULL, the result should be either LEN = 0 or CHARINDEX = 0.

As a result, I have to overwrite these internal functions as user defined functions such as udf_LEN() and udf_CHARINDEX. Then will handle NULL parameters and return the correct result. For example, I use the following CASE statement to check an input parameter:

DECLARE @v_ret int;
SET @v_ret =  CASE
    WHEN @p_string IS NULL THEN 0
    ELSE LEN(@p_string);

When you use internal functions, you have to aware NULL's implication or propagation in your result.

Read More...

Collapse...