Geeks With Blogs

News Locations of visitors to this page


Graeme Reisinger Welcome to my Office. My Other Office.

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.

Posted on Wednesday, December 29, 2010 7:40 PM SQL Server | Back to top


Comments on this post: SQL Server Dynamic Where Clause Column Names

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


Copyright © HighAltitudeCoder | Powered by: GeeksWithBlogs.net