ReasonableRates.sql

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