Geeks With Blogs
Graeme Reisinger Welcome to my Office. My Other Office.

With Dynamic SQL, one stored proc can do the work of many...

Over the years, I have worked with a variety of different clients.  It seems that every client has unique and different needs.  In some situations, you may find that dynamic sql fills the void better than other solutions available to you.

One scenario I have encountered in the past is one where the client wants the capability to run aggregate information at run time, but for any start month and number of time periods.  There are many ways to accomplish this, but assuming you are in the situation where you are required to accomplish this task in the SQL Server environment itself, I will show you how to do that, using the Adventureworks database as an example from which you might develop your own solution tailored to your business environment.

Consider the following SQL:

SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, OrderDate
ORDER BY    SalesPersonID, OrderDate

 

This will return a count of all sales made by all sales people within a given month, oriented vertically.  This isn't very human-friendly, however, as it requires the reader to pick through all the data, if for example he/she wants to see a comprehensive picture of all sales for sales person 278.

A more readable format would be to display the data horizontally, as such:

USE AdventureWorks
GO
 
WITH EmployeeSalesByMonth (OrderDate, SalesPersonID, SalesBySalesPersonThisMonth) AS
(
      SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS 
      SalesBySalesPersonThisMonth
      FROM Sales.SalesOrderHeader
      WHERE SalesPersonID IS NOT NULL
      GROUP BY SalesPersonID, OrderDate
)
 
SELECT * FROM
      (SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS
      SalesBySalesPersonThisMonth
      FROM Sales.SalesOrderHeader
      WHERE SalesPersonID IS NOT NULL
      GROUP BY SalesPersonID, OrderDate
      HAVING MIN(OrderDate) = '7/1/2001'
      --ORDER BY SalesPersonID, OrderDate
      ) AS ESM1
LEFT OUTER JOIN EmployeeSalesByMonth AS ESM2
ON ESM1.SalesPersonID = ESM2.SalesPersonID
AND MONTH(ESM1.OrderDate) + 1 = MONTH(ESM2.OrderDate)
AND YEAR(ESM1.OrderDate) = YEAR(ESM2.OrderDate)
LEFT OUTER JOIN EmployeeSalesByMonth AS ESM3
ON ESM2.SalesPersonID = ESM3.SalesPersonID
AND MONTH(ESM2.OrderDate) + 1 = MONTH(ESM3.OrderDate)
AND YEAR(ESM2.OrderDate) = YEAR(ESM3.OrderDate)
ORDER BY    ESM1.SalesPersonID, ESM1.OrderDate

 

Putting together your stored procedure in such a manner will be relatively straightforward from this point onward.  However what it is likely your client will show this new report to his manager, and the manager might request the same report, while allowing him to choose any number of months he wishes.  You could either create a stored procedure for every number of months imagineable (imagine creating the proc that returns five years of data!).  Or, you could create one stored procedure that creates this information dynamically.

To do this, you will need to accept as parameters a start date, and number of months the client wishes to run the report for.  Once you have this information, you will assemble the query within your stored procedure, and then finally execute it using the (implied) EXECUTE IMMEDIATE functionality of SQL Server:

 
USE AdventureWorks
GO
 
IF EXISTS
      (SELECT * FROM sys.objects
      WHERE type = 'P'
      AND name = 'ProcSalesReportDynamic')
DROP PROCEDURE dbo.ProcSalesReportDynamic
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE dbo.ProcSalesReportDynamic (
      @StartDate                          DATETIME,
      @NumberOfMonths               INT
)
 
AS
BEGIN
 
      SET NOCOUNT ON;
 
      DECLARE @SQL                        VARCHAR(MAX);
      DECLARE @CurrentMonth   INT;
 
      SET @CurrentMonth = 1;
      SET @SQL = 'WITH EmployeeSalesByMonth (OrderDate, SalesPersonID, SalesBySalesPersonThisMonth) AS
      (
            SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth
            FROM Sales.SalesOrderHeader
            WHERE SalesPersonID IS NOT NULL
            GROUP BY SalesPersonID, OrderDate
      --    ORDER BY    SalesPersonID, OrderDate
      )
 
SELECT * FROM
      (SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth
      FROM Sales.SalesOrderHeader
      WHERE SalesPersonID IS NOT NULL
      GROUP BY SalesPersonID, OrderDate
      HAVING MIN(OrderDate) = ''' + CAST(@StartDate AS VARCHAR(50)) + '''
      --ORDER BY SalesPersonID, OrderDate
      ) AS ESM1';
 
      WHILE @CurrentMonth < @NumberOfMonths
      BEGIN
 
            SET @SQL = @SQL + '
LEFT OUTER JOIN EmployeeSalesByMonth AS ESM'
            + CAST(@CurrentMonth + 1 AS VARCHAR(2))
            + '
ON ESM'
            + CAST(@CurrentMonth AS VARCHAR(2))
            + '.SalesPersonID = ESM'
            + CAST(@CurrentMonth + 1 AS VARCHAR(2))
            + '.SalesPersonID
AND MONTH(ESM'
            + CAST(@CurrentMonth AS VARCHAR(2))
            + '.OrderDate) + 1 = MONTH(ESM'
            + CAST(@CurrentMonth + 1 AS VARCHAR(2))
            + '.OrderDate)
AND YEAR(ESM'
            + CAST(@CurrentMonth AS VARCHAR(2))
            + '.OrderDate) = YEAR(ESM'
            + CAST(@CurrentMonth + 1 AS VARCHAR(2))
            + '.OrderDate)';
 
            SET @CurrentMonth = @CurrentMonth + 1;
 
      END -- End WHILE
 
      SET @SQL = @SQL + '
ORDER BY    ESM1.SalesPersonID, ESM1.OrderDate'
 
--PRINT @SQL;
EXEC (@SQL);
 
END; --End dbo.ProcSalesReportDynamic
GO

 

Not bad, right?  (At least it beats writing two, three, twelve, or sixty stored procedures!)

When you execute this for many months, you may notice that the columns begin to become confusing.  Once a column name repeats, it contains the same name for the sequence to be repeated for the next month; the only way to tell which column is which is to look at the data itself.  I thought about correcting this, but for the sake of keeping this post as short as possible, as well as giving you the reader a chance to update the dynamically created SQL as you see fit, I thought it best to leave this correction out.

I will give you a hint, though, and that is to change the dynamically created 'SELECT *' into an explicitly named column list.

Lastly, beware the drawbacks of using dynamic SQL:

  • Your code can blow up at run time.  If you're using SQL Server 2000 or earlier, the max size limit of a VARCHAR is 8000.  That means the maximum size of your dyamically executed SQL will be 8000 characters long.  A client or consumer that attempts to run a proc that terminates prematurely because it has run out of storage space for the string will receive some sort of SQL error.  While you may be familiar with the error, the consumer or client will not.
  • If you are using SQL Server 2005 or later, you will have the VARCHAR(MAX) data type available to you.  This is both a good and a bad thing.  You will not have the size limitation you would normally encounter with SQL Server 2000.  (Well, the string can only be 2Gb long - but is that really a limitation?)  Instead, you could bump up against the limitation that your SQL takes one and a half years to execute; the client or consumer could hang indefinitely.

Either (perhaps both) of the scenarios described above are ones you should plan for, so you can avoid getting dirty looks from your boss.  If your SQL creeps into the territory where it is beginning to take a long time to run, you may find yourself researching ways to improve your statement either through indexing or more likely optimization of the query itself.

Posted on Tuesday, June 15, 2010 9:32 PM SQL Server | Back to top


Comments on this post: SQL Server Dynamic SQL - A Comprehensive Example

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © HighAltitudeCoder | Powered by: GeeksWithBlogs.net