Possible links I left to a question on SE network

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @QuestionId INT = ##QuestionId:int?7053818##; -- QuestionId: PostId of the Question - NOT the answer
DECLARE @SoUserId INT = ##UserId:int?2642059##;
DECLARE @AccountId INT;

SELECT @AccountId = AccountId
FROM   Users
WHERE  Id = @SoUserId;

DECLARE @PostIdPredicate VARCHAR(8000) = 
CONCAT('Text LIKE ''%', @QuestionId, '%''', 
( SELECT CONCAT('OR Text LIKE ''%', Id, '%''') FROM Posts WHERE ParentId = @QuestionId FOR XML PATH('') )
);

CREATE TABLE #Results
  (
     DBName    SYSNAME NOT NULL DEFAULT DB_NAME(),
     PostId    INT NOT NULL,
     IsComment BIT NOT NULL
  )

DECLARE @DynSql NVARCHAR(MAX) = CONCAT(N'

DECLARE @SiteUserId INT;

SELECT @SiteUserId = Id
FROM Users 
WHERE @AccountId = AccountId;

IF @SiteUserId IS NULL
  RETURN

INSERT INTO #Results(PostId, IsComment)
SELECT Id,
       0 AS IsComment
FROM Posts   
CROSS APPLY (SELECT Body AS Text) CA
WHERE OwnerUserId = @SiteUserId
AND (', @PostIdPredicate, ')
UNION ALL
SELECT PostId,
       1 AS IsComment
FROM Comments  
WHERE UserId = @SiteUserId
AND (', @PostIdPredicate, ')

');
DECLARE @db_execute_Sql NVARCHAR(1000);
DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT QUOTENAME(name) + '.sys.sp_executesql'
    FROM   sys.databases
    WHERE  database_id > 5;

OPEN @C1;

FETCH NEXT FROM @C1 INTO @db_execute_Sql;

WHILE @@FETCH_STATUS = 0
  BEGIN
      EXEC @db_execute_Sql
        @DynSql,
        N'@AccountId INT',
        @AccountId = @AccountId;

      FETCH NEXT FROM @C1 INTO @db_execute_Sql;
  END

SELECT *
FROM   #Results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results