Retrieving the list of URL links from Product Procedures - TSR

Here is a script to run on the Products database that will list the links in your Product Procedures.

/****** Script for SelectTopNRows command from SSMS ******/
Declare @ProcedureDetailsID as integer
Declare @StepID as integer
Declare @StepName as varchar(max)
Declare @Loc as integer
Declare @URL as varchar(500)

CREATE TABLE #TEMPProcedures
(
ProcedureDetailsID integer,
StepID integer,
URL varchar(500)
)
Declare PrefStringCursor Cursor for
SELECT [ProcedureDetailsId]
,[StepId]
,[StepName]
FROM [ProcedureSteps] where stepname like '%href%'

Open PrefStringCursor
Set @Loc = 0
Fetch Next from PrefStringCursor INTO @ProcedureDetailsID, @StepID, @StepName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Loc = CHARINDEX('HREF=',@StepName,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@StepName,@Loc+6,CHARINDEX('"',@StepName, @Loc+7)-@Loc-6)
INSERT INTO #TEMPProcedures VALUES (@ProcedureDetailsID, @StepID, @URL)
Set @Loc = CHARINDEX ('HREF=',@StepName, @Loc+7)
END
Fetch Next from PrefStringCursor INTO @ProcedureDetailsID, @StepID, @StepName
Set @Loc = 0
END
Close PrefStringCursor
Deallocate PrefStringCursor

Select Products.Name as ProductName, PD.Name as ProcedureName, StepID, URL from #TEMPProcedures
INNER JOIN ProcedureDetails PD on PD.ProcedureDetailsId = #TEMPProcedures.ProcedureDetailsId
INNER JOIN Procedures P on P.ProcedureId = PD.ProcedureId
INNER JOIN Products on Products.ProductId = P.ProductId
Drop Table #TEMPProcedures