Let's start with your input parameters, since that is SEDE-specific. Did you know you can add parameter hints to make the experience easier for users? See the SEDE Help Page. So we could do something like this:
-- SearchKeywords: Search for word(s) "No need for quote marks"
DECLARE @search_keywords NVARCHAR(MAX) = ##SearchKeywords:string##;
-- SearchTypeId: post / comment "No need for quote marks"
DECLARE @search_type_id NVARCHAR(MAX) = ##SearchTypeId:string##;##;
That way it looks like this instead, including the little tool tip:
That said, there are some things we could improve. As it stands, when you search for a post there are a few deficiencies in the result set:
- No indication whether it is a Question, Answer, or Other type of post.
This is easy to improve. Let's declare two new variables. We will reuse them for your comments query, so let's declare them at the top.
DECLARE @question INT = (SELECT Id FROM PostTypes WHERE Name = 'Question');
DECLARE @answer INT = (SELECT Id FROM PostTypes WHERE Name = 'Answer');
Then we can simply use a CASE statement to add in the type of post, like this:
, CASE
WHEN PostTypeId = @Question THEN 'Question'
WHEN PostTypeId = @Answer THEN 'Answer'
ELSE 'Other'
END AS [Post Type]
This will show you another deficiency:
- Non-Question posts are missing tags!
The reason, as I'm sure you know, is that only Question posts have Tags. What we can do then is use a self-referencing join to get the Tags from the parent questions, again using a CASE statement, like this:
, CASE
WHEN PostTypeId = @Question THEN Tags
WHEN PostTypeId = @Answer THEN (SELECT parent.Tags FROM Posts AS parent INNER JOIN Posts AS child ON child.ParentId = Parent.Id WHERE child.Id = Posts.Id)
ELSE NULL END AS [Tags]
You could break down that one-liner subquery with line breaks, if it makes it easier to read for you.
So we're in pretty good shape for the post search now! Let's look at the comment query.
I think it would be good information to know what kind of post the comment was made on, so why don't we add a join to the Posts table, and get that information from there, using again a CASE statement:
SELECT TOP 10
Comments.Id AS [Comment Link]
, Comments.UserId AS [User Link]
, CASE
WHEN Posts.PostTypeId = @Question THEN 'Question'
WHEN Posts.PostTypeId = @Answer THEN 'Answer'
ELSE 'Other'
END AS [Post Type]
, Comments.Score
, Comments.Text
FROM Comments
INNER JOIN Posts ON Posts.Id = Comments.PostId
Note that I had to reference the tables for each column otherwise it would be an ambiguous join and give you an error.
If you wanted to you could also use the same method as above to get the post tags, although that may not be desired with comments. Also, in both cases, perhaps consider adding the CreationDate field which is useful for sorting.
With everything put together, I came up with this query:
-- SearchKeywords: Search for word(s) "No need for quote marks"
DECLARE @search_keywords NVARCHAR(MAX) = ##SearchKeywords:string##;
-- SearchTypeId: post / comment "No need for quote marks"
DECLARE @search_type_id NVARCHAR(MAX) = ##SearchTypeId:string##;
DECLARE @question INT = (SELECT Id FROM PostTypes WHERE Name = 'Question');
DECLARE @answer INT = (SELECT Id FROM PostTypes WHERE Name = 'Answer');
IF (LOWER(@search_type_id) = 'post')
BEGIN
SELECT
Id AS [Post Link]
, OwnerUserId AS [User Link]
, Score
, CASE
WHEN PostTypeId = @Question THEN 'Question'
WHEN PostTypeId = @Answer THEN 'Answer'
ELSE 'Other'
END AS [Post Type]
, CASE
WHEN PostTypeId = @Question THEN Tags
WHEN PostTypeId = @Answer THEN (SELECT parent.Tags FROM Posts AS parent INNER JOIN Posts AS child ON child.ParentId = Parent.Id WHERE child.Id = Posts.Id)
ELSE NULL END AS [Tags]
, CreationDate
FROM Posts
WHERE
LOWER(Body) LIKE LOWER(
CONCAT('% ', REPLACE(@search_keywords, ' ', '%%'), ' %')
)
ORDER BY Score DESC;
END
ELSE IF (LOWER(@search_type_id) = 'comment')
BEGIN
SELECT
Comments.Id AS [Comment Link]
, Comments.UserId AS [User Link]
, CASE
WHEN Posts.PostTypeId = @Question THEN 'Question'
WHEN Posts.PostTypeId = @Answer THEN 'Answer'
ELSE 'Other'
END AS [Post Type]
, Comments.Score
, Comments.Text
, Comments.CreationDate AS [Comment Creation Date]
, Posts.CreationDate AS [Post Creation Date]
FROM Comments
INNER JOIN Posts ON Posts.Id = Comments.PostId
WHERE
LOWER(Text) LIKE LOWER(
CONCAT('% ', REPLACE(@search_keywords, ' ', '%%'), ' %')
)
ORDER BY Score DESC;
END
ELSE
BEGIN
PRINT 'SearchTypeId must either be "comment" or "post".';
END
