CREATE TABLE #results ( db sysname, Id ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers Meta

Q&A about the site for Drupal developers and administrators

-- This version only 2 keywords

CREATE TABLE #results (
  db           sysname,
  Id           int,
  Title        nvarchar(250),
  Body         nvarchar(max),
  ViewCount    int,
  Score        int,
  CreationDate datetime);

DECLARE @exec nvarchar(500), @sql nvarchar(max), @c cursor;
declare @TitleName nvarchar(100) = ##TitleName:string##
declare @TagName2 nvarchar(100) = ##TagName2:string##
-- declare @TagName3 nvarchar(100) = ##TagName3:string##
-- TagName: The tag you're looking for


SELECT @sql = N'INSERT #results
  (db,Id,Title,Body,ViewCount,Score,CreationDate)
  SELECT DB_NAME(), p.Id, p.Title, p.Body, p.ViewCount, p.Score, p.CreationDate
  FROM Posts AS p 
  WHERE     p.PostTypeId = 1 AND
  p.Title LIKE ''' + cast(@TitleName as nvarchar) + ''' 
  AND p.Body LIKE ''' + cast(@TagName2 as nvarchar) + ''' 
  ';

SET @c = cursor FOR SELECT database_name
  FROM dbo.sede_databases WHERE notes IS NULL;

OPEN @c;
FETCH NEXT FROM @c INTO @exec;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@exec) + N'.sys.sp_executesql';
    EXEC @exec @sql, N'@AccountId int', ##AccountId:int##;
  FETCH NEXT FROM @c INTO @exec;
END

SELECT Site        = sd.long_name, 
       ID          = r.Id,
       Question    = CONCAT(sd.site_url, '/q/', r.Id, '|', r.Title),
       Posttitle   = r.Title,
       Postbody    = r.Body,
       [# views]   = r.ViewCount,
       r.Score,
       [Posted on] = r.CreationDate
  FROM dbo.sede_databases AS sd
  INNER JOIN #results AS r 
     ON r.db = sd.database_name
  ORDER BY r.ViewCount DESC;

Enter Parameters

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