/blog/code/2012/03/16/stored-procedure-for-obtaining-wf4-bookmarks

getwf4bookmarks.sql

-- =============================================
-- Author:      Steve
-- Create date: Auguest 2, 2010
-- Description: Converts the Pending Bookmarks into rows
-- =============================================
CREATE PROCEDURE [dbo].[workflow_Get_Bookmarks]
    @instanceID uniqueidentifier = NULL
AS
BEGIN
    SET NOCOUNT ON;
     
    DECLARE @Bookmarks TABLE(
        ID uniqueidentifier,
        Bookmark varchar(1000),
        Created datetime
    )
     
    Declare @ID as uniqueidentifier
    Declare @Bookmark as nvarchar(1000)
    Declare @Created as datetime
 
    Declare BookmarkCursor CURSOR FAST_FORWARD FOR
 
    SELECT [Id],[BlockingBookmarks], [CreationTime]
    FROM [authdb].[System.Activities.DurableInstancing].[InstancesTable]
    ORDER BY [CreationTime] desc
 
    OPEN BookmarkCursor
    FETCH NEXT FROM BookmarkCursor
    INTO @ID, @Bookmark, @Created
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @Bookmarks(ID, Bookmark, Created)
            SELECT @ID, REPLACE(string,']',''), @Created
            FROM [authdb].[dbo].[SplitToRows] (
               '['
              ,@Bookmark)
 
         FETCH NEXT FROM BookmarkCursor
         INTO @ID, @Bookmark, @Created
    END
 
    CLOSE BookmarkCursor
    DEALLOCATE BookmarkCursor
     
 
    IF @instanceID IS NULL
        BEGIN
            SELECT *
            FROM @Bookmarks
            WHERE (Bookmark <> null) or (Bookmark <> '')
        END
    ELSE
        BEGIN
            SELECT *
            FROM @Bookmarks
            WHERE (Bookmark <> null) or (Bookmark <> '') AND
            ID = @instanceID
        END
 END
 
GO
Ok, now there's one other component required, and that's a table function to convert the initial bookmark string ([value][value][value]) into living on their own rows

/***********************************************************/
/** This is to help convert Instance Bookmarks to a table **/
/***********************************************************/
CREATE FUNCTION [dbo].[SplitToRows] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn as id, LTRIM(RTRIM(SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))) AS string
    FROM Pieces
  )
 
 
GO