Search Content of Posts I'm Active On

1

Please login or register to vote for this query.

(click on this box to dismiss)

Search the textual content of Comments, Answers, and their associated Question - in which I commented, answered, or posed the question - for specific text. Disclaimer: I don't normally work much with SQL, just the basics. Please forgive any latency, redundancy, or inefficiencies in the following query. I'm sure there are plenty of improvements to be made.

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @pattern nvarchar(100) = UPPER(##Pattern:string##)
DECLARE @commparent TABLE (Id int)
DECLARE @postparent TABLE (Id int)
DECLARE @posts TABLE (Id int, ParentId int, CreationDate datetime, Body nvarchar(max))
DECLARE @comments TABLE (Id int, PostId int, ParentId int, CreationDate datetime, Text nvarchar(600))

INSERT INTO @commparent
  SELECT
    PostId as [Id]
  FROM Comments
  WHERE UserId = ##UserID##

INSERT INTO @postparent
  SELECT 
    Id
  FROM Posts
  WHERE (OwnerUserId = ##UserID##
  OR Id IN(select Id FROM @commparent))
  AND ParentId IS NULL
  UNION
  SELECT
    ParentId as [Id]
  FROM Posts
  WHERE (OwnerUserId = ##UserID##
  OR Id IN(select Id FROM @commparent))
  AND ParentId IS NOT NULL
  
INSERT INTO @posts
  SELECT
    p.Id,
    p.ParentId,
    p.CreationDate,
    p.Body
  FROM Posts p
  WHERE p.Id IN(SELECT Id FROM @postparent)
  OR p.ParentId IN(SELECT Id FROM @postparent)
  
INSERT INTO @comments
  SELECT
    c.Id,
    c.PostId,
    IIF(p.ParentId IS NULL, p.Id, p.ParentId) AS [ParentId],
    c.CreationDate,
    c.Text
  FROM Comments c
  LEFT JOIN @posts p ON p.Id = c.PostId
  WHERE c.PostId IN(SELECT Id FROM @posts)
           
SELECT
  CreationDate AS [Creation Date],
  [Post Link],
  [Comment Link]
FROM
  (SELECT
     Body AS [Content],
     CreationDate,
     Id AS [Post Link],
     null AS [Comment Link],
     IIF(ParentId IS NULL, Id, ParentId) AS [ORDER]
   FROM @posts
   UNION
   SELECT
     Text AS [Content],
     CreationDate,
     PostId AS [Post Link],
     Id AS [Comment Link],
     ParentId AS [ORDER]
   FROM @comments
  ) s
WHERE UPPER([Content]) LIKE '%' + @pattern + '%'
ORDER BY [ORDER] DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results