Retrieving the list of links from Products and Services - TSR Follow
Here is a script which when run on the Products database, will return all of the links in all of the Product fields, except for Documents Related to this Product, and Procedures. Procedures is another article.
/****** Script for SelectTopNRows command from SSMS ******/
Declare @ProductID as integer
Declare @CategoryID as integer
Declare @ProductName as varchar(250)
Declare @Description as varchar(max)
Declare @Comments as varchar(max)
Declare @PromotionsCorner as varchar(max)
Declare @SalesTips as varchar(max)
Declare @RelatedProducts as varchar(max)
Declare @Loc as integer
Declare @URL as varchar(500)
CREATE TABLE #tempProducts
(
ProductID int,
CategoryID int,
ProductName varchar(max),
ProductSection varchar(250),
URL varchar(500)
)
Declare PrefStringCursor Cursor for
SELECT [ProductID]
,[CategoryId]
,[Name]
,[Description]
,[Comments]
,[PromotionsCorner]
,[SalesTips]
,[RelatedProducts]
FROM [Products]
Open PrefStringCursor
Fetch Next from PrefStringCursor INTO @ProductID, @CategoryID, @ProductName, @Description, @Comments, @PromotionsCorner, @SalesTips, @RelatedProducts
WHILE @@FETCH_STATUS = 0
BEGIN
--Description
Set @Loc = 0
Set @Loc = CHARINDEX('HREF=',@Description,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@Description,@Loc+6,CHARINDEX('"',@Description, @Loc+7)-@Loc-6)
INSERT INTO #tempProducts VALUES (@ProductID, @CategoryID, @ProductName, 'Description', @URL)
Set @Loc = CHARINDEX ('HREF=',@Description, @Loc+7)
END
--Comments
Set @Loc = 0
Set @Loc = CHARINDEX('HREF=',@Comments,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@Comments,@Loc+6,CHARINDEX('"',@Comments, @Loc+7)-@Loc-6)
INSERT INTO #tempProducts VALUES (@ProductID, @CategoryID, @ProductName, 'Comments', @URL)
Set @Loc = CHARINDEX ('HREF=',@Comments, @Loc+7)
END
--PromotionsCorner
Set @Loc = 0
Set @Loc = CHARINDEX('HREF=',@PromotionsCorner,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@PromotionsCorner,@Loc+6,CHARINDEX('"',@PromotionsCorner, @Loc+7)-@Loc-6)
INSERT INTO #tempProducts VALUES (@ProductID, @CategoryID, @ProductName, 'Promotions Corner', @URL)
Set @Loc = CHARINDEX ('HREF=',@PromotionsCorner, @Loc+7)
END
--SalesTips
Set @Loc = 0
Set @Loc = CHARINDEX('HREF=',@SalesTips,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@SalesTips,@Loc+6,CHARINDEX('"',@SalesTips, @Loc+7)-@Loc-6)
INSERT INTO #tempProducts VALUES (@ProductID, @CategoryID, @ProductName, 'Sales Tips', @URL)
Set @Loc = CHARINDEX ('HREF=',@SalesTips, @Loc+7)
END
--RelatedProducts
Set @Loc = 0
Set @Loc = CHARINDEX('HREF=',@RelatedProducts,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@RelatedProducts,@Loc+6,CHARINDEX('"',@RelatedProducts, @Loc+7)-@Loc-6)
INSERT INTO #tempProducts VALUES (@ProductID, @CategoryID, @ProductName, 'Related Products', @URL)
Set @Loc = CHARINDEX ('HREF=',@RelatedProducts, @Loc+7)
END
Fetch Next from PrefStringCursor INTO @ProductID, @CategoryID, @ProductName, @Description, @Comments, @PromotionsCorner, @SalesTips, @RelatedProducts
Set @Loc = 0
END
Close PrefStringCursor
Deallocate PrefStringCursor
Select Name as ProductCategory, ProductName, ProductSection, URL from #TempProducts INNER JOIN ProductCategories on #TempProducts.CategoryID = ProductCategories.CategoryId
Drop Table #tempProducts
Comments
0 comments
Please sign in to leave a comment.