Let me start this post by saying if you are searching for the capability to do this, there is a very good chance you have problems with your current database architecture.
Having said that, I realize you may not have the option of normalizing your database, and have no choice but to work with what you've got. If this is the case, you may find the following post a useful workaround to whatever problem is causing you to search for a dynamic WHERE clause.
A dynamic WHERE clause might be useful if you wish to search for an ID or a GUID generically from a junction table, but don't know what that particular item or object type (column name) might be, until run time. Or perhaps the information is coming from a denormalized table containing many types of objects, with similar column titles between each. For example, you might need Part ID 345698 and the part name from the dbo.MyJunction table. The next time you run the same query, you may need Assembly ID 43578 and the assembly name from the same dbo.MyJunction table.
I'm going to show you how to accomplish this.
The following example uses a Function, however if you need to use dynamic SQL along with the Execute statement, you will have to use some other kind of object, like maybe a stored proc.
|
USE [YourDbName]
GO
/****** Object: UserDefinedFunction [dbo].fnDynamicWhereClause ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(
SELECT * FROM SYS.OBJECTS
WHERE name = 'fnDynamicWhereClause'
AND type = 'TF'
)
DROP FUNCTION [dbo].[fnDynamicWhereClause]
GO
CREATE FUNCTION [dbo].[fnDynamicWhereClause](
@ID INT,
@TableName VARCHAR(255)
)
RETURNS @Table TABLE (
ID INT,
Name VARCHAR(255)
)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
INSERT INTO @Table VALUES
(
SELECT ID, Name
FROM SomeJunctionTable
WHERE @ID =
CASE UPPER(@TableName)
WHEN 'ASSEMBLY' THEN Assembly
WHEN 'PART' THEN Part
WHEN 'MODULE' THEN Module
END
)
RETURN
END -- End Function
|
The important thing here is the SQL contained in the WHERE clause. In the end, the SQL executed will look like:
SELECT ID, Name FROM SomeJunctionTable WHERE 345698 = Part
- or -
SELECT ID, Name FROM SomeJunctionTable WHERE 345698 = Assembly
This is slightly different from what you're probably used to, where the column name is declared first, and then the search condition is on the right side of the equals sign:
SELECT ID, Name FROM SomeJunctionTable WHERE Part = 345698
If you attempted to reorder the INSERT statement so the column name comes first, you would receive an error at compile time because SQL Server is unable to determine which column name to associate with the value. If you switch the two, SQL Server is ok with that because the item on the left-hand side of the equals sign is known (for example, a value of 345698), and the value on the right-hand side of the statement can be evaluated to a column name.
While I didn't expect this to work when I was working on it for the first time, I was pleasantly surprised when it did! It saved me a lot of work in the end, so I figured I'd share it with anyone else out there looking for this capability.
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.
When you need the earlier of or later of two dates and NULL handling capability.
Add flexibility to your database with parsing functions.