Skip to content

Instantly share code, notes, and snippets.

@sitefinitysteve
Created July 24, 2020 15:20
Show Gist options
  • Save sitefinitysteve/2fe636190a2269134c5ea3c65b38c54b to your computer and use it in GitHub Desktop.
Save sitefinitysteve/2fe636190a2269134c5ea3c65b38c54b to your computer and use it in GitHub Desktop.
/blog/code/2012/03/16/stored-procedure-for-obtaining-wf4-bookmarks
-- =============================================
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment