Cursors on the Fly

In general, I don’t like cursors. My experience has been that virtually any business logic can be implemented through joining relational data sets, especially after the introduction of the CROSS APPLY join type in SQL Server 2005. Having said that, there are a few specific instances when a cursor could be handy. One such case is when individual actions must be performed on records from a sub query, but the sub query definition is not known prior to run time. In that instance, creating a cursor definition on-the-fly can be a useful technique.

Let’s take a closer look.

Business Case: An application makes use of databases in a hierarchical manner such that a single, top-level database contains connection information to multiple, second-level databases. A use case exists such that select information from the top-level database must be used to identify which second-level databases are to be queried.

Environment: The complication with this request is that the specific information from the top-level database is not known prior to run time, thus the specific names of the second-level databases are also not known. Because the second-level names are not known, traditional join or cursor syntax will not work as parameters for variable substitution are not allowed.

Approach: We will use the API cursor definition procedures to create a cursor on-the-fly based on information queried during run time.

Demonstration Preparation: For demonstration purposes, first create two databases on your server in your default configuration. The first database can be called DB and the second can be called DB2. We do not need any special configuration on the databases; whatever you have as your server default will do. The first database (DB) will simulate our top-level database as well as one of the second-level databases. The second database (DB2) will simulate another second-level database. We will create a parent table in the top-level database called tblOne; we will create a second table called tblTwo in the top-level database and as well as in the second database. For this example we will be working within the “dbo” schema.

-- If our parent table already exists, drop it
IF OBJECT_ID(N'DB.dbo.tblOne') IS NOT NULL
    DROP TABLE DB.dbo.tblOne
;

-- Create the parent table
CREATE TABLE DB.dbo.tblOne (
    ID INT NOT NULL
    , DatabaseName VARCHAR(10) NOT NULL
    , MinIDToReturn TINYINT)
;

-- Populate the parent table
INSERT INTO DB.dbo.tblOne
VALUES
    (1, N'DB', 1)
    , (2, N'DB2', 2)
;

-- If the first secondary table already exists, drop it
IF OBJECT_ID(N'DB.dbo.tblTwo') IS NOT NULL
    DROP TABLE DB.dbo.tblTwo
;

-- Create the first secondary table
CREATE TABLE DB.dbo.tblTwo (
    ID INT NOT NULL
    , Value VARCHAR(10) NOT NULL)
;

-- Populate the first secondary table
INSERT INTO DB.dbo.tblTwo
VALUES
    (1, N'DB - 1')
    , (2, N'DB - 2')
    , (3, N'DB - 3')
;

-- If the second secondary table already exists, drop it
IF OBJECT_ID(N'DB2.dbo.tblTwo') IS NOT NULL
    DROP TABLE DB2.dbo.tblTwo
;

-- Create the second secondary table
CREATE TABLE DB2.dbo.tblTwo (
    ID INT NOT NULL
    , Value VARCHAR(10) NOT NULL)
;

-- Populate the second secondary table
INSERT INTO DB2.dbo.tblTwo
VALUES
    (1, N'DB2 - 4')
    , (2, N'DB2 - 5')
    , (3, N'DB2 - 6')
    , (4, N'DB2 - 7')
;

Solution: We will start by creating a cursor via the normal TSQL commands to return the connection (database) and return parameter from the top-level database. Then we will use a few of the cursor API stored procedures to create child cursors on-the-fly.

-- Declare variables
DECLARE
    @strDBName VARCHAR(10) -- one variable from the primary cursor
    , @intMinID TINYINT -- second variable from the primary cursor
    , @strSQL VARCHAR(255) -- string for child cursor definition
    , @crs2 INT -- cursor handle for child cursor
    , @intRowCount INT -- placeholder for child cursor creation
;
DECLARE @tbl TABLE ( -- storage for the child cursor
    ID INT
    , Value VARCHAR (10))
;

-- Declare the primary cursor
DECLARE crs CURSOR READ_ONLY
FOR
    SELECT
        DatabaseName
        , MinIDToReturn
    FROM DB.dbo.tblOne
;

-- Open the primary cursor
OPEN crs;

-- Prepare the primary cursor for iteration
FETCH NEXT FROM crs INTO @strDBName, @intMinID;

-- Scroll through the primary cursor records
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Set the dynamic cursor record set
    --   This is the point of an on-the-fly cursor: creating a
    --   definition ad hoc. This example uses two techniques: ad hoc
    --   string creation (@strDBName) as well as parameterization (@intID)
    SET @strSQL = 'SELECT ID, Value FROM ['
        + @strDBName
        + '].dbo.tblTwo WHERE ID > @intID;'
    ;

    -- Display the parameters we are working with, FYI
    SELECT
        'DatabaseName' = @strDBName
        , 'SQL' = @strSQL
        , '@intID' = @intMinID
    ;

    -- Open the secondary cursor
    --   This procedure will return an empty result set with the actual
    --   column headings
    EXECUTE sp_cursoropen
        @cursor = @crs2 OUTPUT
        , @stmt = @strSQL
        , @scrollopt = 4104 -- STATIC (8), PARAMETERIZED_STMT (4096)
        , @ccopt = 8193 -- READ_ONLY (1), ALLOW_DIRECT (8192)
        , @rowcount = @intRowCount OUTPUT
        , @paramdef = N'@intID INT'
        , @intID = @intMinID
    ;

    -- Scroll through the secondary cursor records
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Store the secondary cursor record and iterate the secondary
        -- cursor
        --   While we are simply storing the records in this
        --   example, if you are creating a cursor you should be doing
        --   something that is not possible through relational joins
        INSERT INTO @tbl (ID, Value)
        EXECUTE sp_cursorfetch
            @cursor = @crs2
            , @fetchtype = 2 -- NEXT (0x0002)
            , @nrows = 1 -- number of rows to return
        ;

    END

    -- Display the secondary cursor record set, FYI
    SELECT *
    FROM @tbl
    ;

    -- Close the secondary cursor
    EXECUTE sp_cursorclose @crs2;

    -- Reset the secondary cursor output table variable
    DELETE FROM @tbl;

    -- Iterate the primary cursor
    FETCH NEXT FROM crs INTO @strDBName, @intMinID;

END

-- Close the primary cursor
CLOSE crs;

-- Release the primary cursor

DEALLOCATE crs;

Taking a look at the result sets from the above statements will show that we did, indeed, connect to both databases and returned rows from the target tables based on the passed parameters from tblOne.

Obviously, this example could be extended to include cross-server connections through connection strings or linked servers, or other more complicated permutations.

Clean Up: Now, let’s clean up. We’ll drop the three test tables; optionally, you could just drop the test databases instead. 😉

-- If our parent table exists, drop it
IF OBJECT_ID(N'DB.dbo.tblOne') IS NOT NULL
    DROP TABLE DB.dbo.tblOne
;

-- If the first secondary table exists, drop it
IF OBJECT_ID(N'DB.dbo.tblTwo') IS NOT NULL
    DROP TABLE DB.dbo.tblTwo
;

-- If the second secondary table exists, drop it
IF OBJECT_ID(N'DB2.dbo.tblTwo') IS NOT NULL
    DROP TABLE DB2.dbo.tblTwo
;

-- Change database context
USE [master];
GO

-- If the DB database exists, drop it
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB')
    DROP DATABASE [DB]
;

-- If the DB2 database exists, drop it
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB2')
    DROP DATABASE [DB2]
;

Final Notes: Now, before anyone sends me fan mail, note that this business case can be handled with dynamic TSQL commands (sp_executesql) instead of API cursor procedures. But, for 25 bonus points, who can suggest at least one reason why that might not be as good an idea?

Also, I intentionally did not refer to this approach as a dynamic cursor. While we did construct the cursor definition dynamically, the term “dynamic” is actually reserved in cursor usage to refer to a setting whereby the data underlying a cursor can be updated through the cursor. In this example, we were read-only; hence, not a “dynamic” cursor.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: