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