Skip to content

Instantly share code, notes, and snippets.

@erikdarlingdata
Created February 12, 2024 14:36
Show Gist options
  • Save erikdarlingdata/d1e5f8faf76d4d6c2c0cab55849f76f8 to your computer and use it in GitHub Desktop.
Save erikdarlingdata/d1e5f8faf76d4d6c2c0cab55849f76f8 to your computer and use it in GitHub Desktop.
CREATE OR ALTER PROCEDURE
dbo.ReasonableRates
(
@Top bigint = 5000,
@DisplayName nvarchar(40) = NULL,
@Reputation integer = NULL,
@OwnerUserId integer = NULL,
@CreationDate datetime = NULL,
@LastActivityDate datetime = NULL,
@PostTypeId integer = NULL,
@Score integer = NULL,
@Title nvarchar(250) = NULL,
@Body nvarchar(MAX) = NULL,
@HasBadges bit = 'false',
@HasComments bit = 'false',
@HasVotes bit = 'false',
@OrderBy sysname = N'Id',
@OrderDir nchar(1) = N'A',
@Debug bit = 'false',
@Exec bit = 'true'
)
AS
BEGIN
/*
XACT_ABORT isn't really necessary here,
but if someone's going to learn from me,
they should learn that it's a generally
good practice to turn it on.
It will roll back all modifications,
rather than just roll back one that
failed, and leave all the others.
*/
SET NOCOUNT, XACT_ABORT ON;
/*You might want to limit ordering to specific columns*/
IF ISNULL(@OrderBy, N'') NOT IN
(
N'DisplayName',
N'Reputation',
N'OwnerUserId',
N'CreationDate',
N'LastActivityDate',
N'PostTypeId',
N'Score',
N'Id'
)
BEGIN
RAISERROR(N'That there column (%s) ain''t allowed, pardner, GIDDYUP!', 1, 0, @OrderBy) WITH NOWAIT;
SET @OrderBy = N'Id';
END;
/*
Sensible ordering maintained
I'm using UPPER here in to protect against case-sensitivity issues
*/
IF UPPER(ISNULL(@OrderDir, N'')) NOT IN
(
N'A',
N'D'
)
BEGIN
RAISERROR(N'That there sort (%s) ain''t allowed, pardner, GIDDYUP!', 1, 0, @OrderDir) WITH NOWAIT;
SET @OrderDir = N'A';
END;
/*
We need these to work with dynamic SQL
They have to be nvarchar for sp_executesql
*/
DECLARE
@SQLString nvarchar(MAX) = N'',
@Filter nvarchar(MAX) = N'',
@nl nchar(2) = NCHAR(10),
@Parameters nvarchar(MAX) =
N'@DisplayName nvarchar(40),
@Reputation integer,
@OwnerUserId integer,
@CreationDate datetime,
@LastActivityDate datetime,
@Score integer,
@PostTypeId integer,
@Title nvarchar(250),
@Body nvarchar(MAX)';
/*
Some notes on the stuff at the top:
* N prefixes to maintain unicode-ness
* @Top isn't parameterized, because:
* https://erikdarling.com/a-parameterization-puzzle-with-top-part-1/
* https://erikdarling.com/a-parameterization-puzzle-with-top-part-2/
* https://erikdarling.com/a-parameterization-puzzle-with-top-follow-up/
* https://erikdarling.com/a-parameterization-puzzle-with-top-percent/
* https://erikdarling.com/parameters-in-top-are-sniffed-too/
* But I'm using QUOTENAME to keep safe from potential SQL injection
* Note that QUOTENAME only takes a single character as input, and
I could have just use '(', but '()' makes the intent a little more clear
* I have a comment in the dynamic SQL block to note the module name
*/
SET @SQLString += N'
SELECT TOP ' + QUOTENAME(@Top, '()') +
N'
/*dbo.ReasonableRates*/
u.DisplayName,
u.Reputation,
p.OwnerUserId,
p.Score,
Tags =
ISNULL(p.Tags, N''N/A: Answer''),
Title =
ISNULL(p.Title, N''N/A: Answer''),
p.CreationDate,
p.LastActivityDate,
p.Body' +
CASE @HasBadges
WHEN 'true'
THEN
N',
BadgeCount =
(
SELECT
ISNULL(COUNT_BIG(*), 0)
FROM dbo.Badges AS b
WHERE b.UserId = u.Id
)'
ELSE N''
END +
CASE @HasComments
WHEN 'true'
THEN
N',
CommentCount =
(
SELECT
ISNULL(COUNT_BIG(*), 0)
FROM dbo.Comments AS c
WHERE c.UserId = u.Id
)'
ELSE N''
END +
CASE @HasVotes
WHEN 'true'
THEN
N',
VoteCount =
(
SELECT
ISNULL(COUNT_BIG(*), 0)
FROM dbo.Votes AS v
WHERE v.PostId = p.Id
)'
ELSE N''
END +
N'
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE 1 = 1 ' + @nl;
/*
It's a lot easier to add a 1 = 1 condition,
and then just append AND/OR logic later, than
it is to chop up strings later
*/
/*
I want to let people use either wildcard or equality searches for strings
To find % in strings, you need to put brackets around it
*/
IF @DisplayName IS NOT NULL
BEGIN
IF @DisplayName LIKE N'[%]'
BEGIN
SET @Filter = @Filter + N' AND u.DisplayName LIKE @DisplayName' + @nl;
END;
ELSE
BEGIN
SET @Filter = @Filter + N' AND u.DisplayName = @DisplayName' + @nl;
END;
END;
/*
For all of these predicates, I'm hardcoding search conditions here.
Writing custom search conditions for each predicate is doable, but
can get really complicated, depending on how you send up the front end.
If you want to pass in an IN list, you'll want to use table valued parameters,
or some updated techniques in Entity Framework that allow for parameterization:
* https://erikdarling.com/dealing-with-unparameterized-in-clauses-from-entity-framework/
* https://erikdarling.com/software-vendor-mistakes-with-sql-server-passing-long-in-clauses-to-queries-from-an-orm/
*/
IF @OwnerUserId IS NOT NULL
BEGIN SET @Filter = @Filter + N' AND p.OwnerUserId = @OwnerUserId ' + @nl; END;
IF @CreationDate IS NOT NULL
BEGIN SET @Filter = @Filter + N' AND p.CreationDate >= @CreationDate' + @nl; END;
IF @LastActivityDate IS NOT NULL
BEGIN SET @Filter = @Filter + N' AND p.LastActivityDate < @LastActivityDate' + @nl; END;
IF @Score IS NOT NULL
BEGIN SET @Filter = @Filter + N' AND p.Score >= @Score' + @nl; END;
IF @PostTypeId IS NOT NULL
BEGIN SET @Filter = @Filter + N' AND p.PostTypeId = @PostTypeId' + @nl; END;
/*More string search flexibility*/
IF @Title IS NOT NULL
BEGIN
IF @Title LIKE N'[%]'
BEGIN
SET @Filter = @Filter + N' AND p.Title LIKE @Title' + @nl;
END;
ELSE
BEGIN
SET @Filter = @Filter + N' AND p.Title = @Title' + @nl;
END;
END;
IF @Body IS NOT NULL
BEGIN
IF @Body LIKE N'[%]'
BEGIN
SET @Filter = @Filter + N'AND p.Body LIKE @Body' + @nl;
END;
ELSE
BEGIN
SET @Filter = @Filter + N'AND p.Body = @Body' + @nl;
END;
END;
/*
Someone might pass in nothing, resulting in a NULL
@Filter variable, which would NULL out our @SQLString
There are other ways to avoid this. This is my preference.
*/
IF @Filter IS NOT NULL
BEGIN
SET @SQLString += @Filter;
END;
/*
We need to do a little more here, because
you're allowed to order by columns from two
tables, and some of the columns exist in both.
The spacing looks a little funny, but it maintains formatting.
In some cases, it would make sense to always have
a unique column as the second ordering element,
so that results stay determinstic.
Ordering by non-unique columns can return different,
but not incorrect, results, especially in parallel plans.
I know, this isn't set up to handle ordering by multiple columns.
Sorry. I just don't feel like it at the moment.
*/
SET @SQLString += N' ORDER BY ' +
@nl +
CASE
WHEN @OrderBy IN (N'DisplayName', N'Reputation')
THEN N' u.'
ELSE N' p.'
END +
QUOTENAME(@OrderBy) +
CASE
WHEN @OrderDir = N'A' THEN N' ASC'
WHEN @OrderDir = N'D' THEN N' DESC'
ELSE N'ASC, NEWID() DESC' /*Ha ha ha, don't actually do this.*/
END +
N';';
/*Print our query if we're debugging*/
IF @Debug = 'true'
BEGIN
PRINT @SQLString;
END;
/*Only execute if we mean to*/
IF @Exec = 'true'
BEGIN
EXEC sys.sp_executesql
@SQLString,
@Parameters,
@DisplayName,
@Reputation,
@OwnerUserId,
@CreationDate,
@LastActivityDate,
@Score,
@PostTypeId,
@Title,
@Body;
END;
END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment