Creating Dynamic Where Clauses

Sometimes you just have to be able to dynamic search criteria in a stored procedure. What’s going to be the best way to accomplish this? You could use dynamic SQL and run the risk that you won’t have compiled execution plans, syntax highlighting, or readable SQL code. That’s not really an elegant solution. Let’s get started with a User table: CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, [EmailAddress] [varchar](255) NOT NULL, [Username] [varchar](50) NOT NULL, [Password] [varchar](256) NOT NULL, [PasswordHint1] [int] NOT NULL, [PasswordHint1Question] [varchar](255) NOT NULL, [PasswordHint1Answer] [varchar](50) NOT NULL, [PasswordHint2] [int] NOT NULL, [PasswordHint2Question] [varchar](255) NOT NULL, [PasswordHint2Answer] [varchar](50) NOT NULL, [FailedLoginAttempts] [smallint] NOT NULL CONSTRAINT[DF_User_FailedLoginAttempts]  DEFAULT ((0)), [AccessLevelId] [int] NOT NULL, [ClientID] [int] NOT NULL, [SiteID] [int] NOT NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserId] ASC ) ); The goal here is to be able to SELECT a user by username for a combination of ClientId and SiteId, but at least one must be present. So, we create a stored procedure: CREATE PROCEDURE Select_UserByUsername @Username VARCHAR, @ClientId INT = NULL, @SiteId    INT = NULL WITH EXECUTE AS OWNER AS /****************************************************************************** * Author:      Jeremiah Peschka * Create Date: 2008/10/06 * Description: Retrieve user information by Username *****************************************************************************/ SET NOCOUNT ON; IF @ClientId IS NULL AND @SiteId IS NULL RAISERROR('At least ClientId or SiteId must be supplied as parameters.', 16, 1);  –– Only return moderately useful information.  Security –– related information is kept far away from the caller –– unless requested specifically. SELECT [UserId] ,[FirstName] ,[LastName] ,[EmailAddress] ,[Username] ,[AccessLevelId] FROM [UtilityWeb].[dbo].[User] WHERE [Username] = @Username AND [ClientId] = COALESCE(@ClientID, ClientId) AND [SiteId]    = COALESCE(@SiteId, SiteId); GO You can even test it with the following SQL: EXEC Select_UserByUsername '' EXEC Select_UserByUsername '', 3 EXEC Select_UserByUsername '', NULL, 7 Now, what’s going on here? This SQL will retrieve relevant user information by Username. Since Username is not 100% unique, it’s necessary to specify that it be associated with either a client or a site. How do we do this? By specifying that the ClientId either be equal to the parameter provided or to itself through the use of COALESCE. For those that don’t know, COALESCE returns the first non-null expression from its arguments. If you supply SiteId but don’t supply ClientId, the WHERE clause will effectively read: WHERE [Username] = @Username AND [ClientId] = ClientId AND [SiteId]   = @SiteId Since ClientId is always going to equal ClientId, the search is only performed on Username and SiteId. N.B. It’s important to note that this technique will only work when the columns you are searching on are NOT NULL columns. If the columns are NULLable, you will not return any rows when you attempt to compare col1 = col1.