Flexible Custom Reports for SQL Server

Flexible Custom Reports for SQL Server

This article is written by developer for other developers, sharing my experience with a technique that I found in Kimberly Tripp's SQL Server blog article titled " Building High Performance Stored Procedures". In her article, she demonstrated a dynamic SQL technique that allows for "Query By Example" screens that allow most fields to be optional. I would strongly encourage you to read her article along with the associated cautionary notes. I would further add that this technique should not be used for any automated reporting tasks. If you have a known report requirement, make a dedicated stored procedure for it!

I am writing this article to share a few refinements as well as a nice way to catch refactoring dependencies when this stored procedure is part of a database project in Visual Studio.  As a point of reference, I used MS SQL 2014 and Visual Studio 2017, but the technique should work with older versions of SQL.

1) Include a reference copy of the QUERY in the stored procedure.  This might create a junk query plan, but it will allow you to find table and field references in Visual Studio.  If this causes performance problems, I would love to hear about it.  If we only had conditional compilation, we could include the reference query in a DEBUG build, but remove it from production builds.  I am aware that I could probably do this with some marker comments and a tricky Perl regular expression, but I try to avoid complexity where I can.

/*
 ** Stored Procedure Comments and History go here **
*/
create procedure Foo_Report_AllInOne
(
   @Key1Id int = null,
   @Key2Id int = null,
   @Name1 varchar(50) = null,
   @Start datetime = null,
   @End datetime = null
)
as
set nocount on;

-- check parameters
if ( @Key1Id is null
    and @Key2Id is null
    and @Name1 is null
    and @StartDate is null
    and @EndDate is null )
begin
    raiserror ('You must supply at least one parameter.', 16, -1);
    return;
end;

declare @ExecStr nvarchar (4000),
        @Recompile  bit = 0;
 
if (1=0)
begin
   -- include a non-executing version of the query with ALL conditional fields referenced, so we
   -- can find this code when refactoring.
   select
     Key1Id, Field1, Key2Id, Field2, Name1, EventDate
     from myschema.FOO
     where 1=1
     and Key1Id = @Key1Id
     and Key2Id = @Key2Id
     and Name1 like @Name1
     and EventDate >= @Start
     and EventDate < @End
end

-- continue with code from the Kimberly Tripp article.

Everything inside the "if (1=0)" code block MUST be a full copy of the dynamic query with all possible options included, otherwise it has no value for field reference and refactoring purposes. Since this query will never execute, it doesn't have to be a logically sound query.  This also allows you to quickly copy and paste new query code (down to the "where 1=1") into your dynamic query after Visual Studio has checked the syntax.

2) Include a print statement at the bottom of the procedure with all of the parameters and the dynamic query statement to be executed.  Ideally you would print a declare statement, set statements and the dynamic code so it could be manually executed for testing and debugging.  In fact, the best way to debug dynamic code is just to comment out the sp_executesql call (uncomment the print statements if needed) and view the generated code.

3) If your query is longer than 4000 characters, use care to avoid truncating and remember to always append to the larger type - @ExecStr in our case.