How do I store a where clause in a variable and execute the sql statement

neil_squires
neil_squires used Ask the Experts™
on
Hi everyone.  I have a search page with 9 fields that users may use to filter a resultset.  This is an asp.net 2.0 project and I'm using a 3 tiered architecture with a Sql Server 2005 back end.  I want the users to be able to filter the results by entering any combination of the filter controls.  

I have been able to get all of the user's selections into a stored procedure.  The problem I'm having now is in building the dynamic where clause.  I have included the code of the SP that I have been using, but am unable to get the where clause to work.

Does anyone have any ideas?
DECLARE @ContactID int
DECLARE @Where1 nvarchar(4000)
 
SET @ContactID = 12
SET @Where1 = '(Contact.ContactID = 18)'
 
SELECT DISTINCT 
                      Contact.ContactID, Contact.ContactFirstName, Contact.ContactLastName, Contact.ContactAddress1, Contact.ContactAddress2, Contact.ContactCity, 
                      Contact.ContactState, Contact.ContactZip, Contact.ContactPhone, Contact.ContactFax, Contact.ContactEmail, Contact.CompanyRepresenting, 
                      CategoryType.CategoryTypeName, Category.CategoryName, Status.StatusName, Staff.StaffFirstName + N' ' + Staff.StaffLastName AS StaffName, 
                      CategoryType.CategoryTypeID, Request.RequestID, Request.StatusID, Request.CategoryID, Request.AssignedToStaffID, Contact.AddedDate, 
                      Contact.AddedBy, Contact.ModifiedDate, Contact.ModifiedBy
FROM         Category INNER JOIN
                      CategoryType ON Category.CategoryTypeID = CategoryType.CategoryTypeID INNER JOIN
                      Request ON Category.CategoryID = Request.CategoryID INNER JOIN
                      Contact ON Request.ContactID = Contact.ContactID INNER JOIN
                      Status ON Request.StatusID = Status.StatusID INNER JOIN
                      Staff ON Request.AssignedToStaffID = Staff.StaffID
	WHERE @Where1
--WHERE     (Contact.ContactID = @ContactID)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
DECLARE @ContactID int
DECLARE @Where1 nvarchar(4000), @sql nvarchar(4000)

 
SET @ContactID = 12
SET @Where1 = '(Contact.ContactID = 18)'
 
set @sql = 'SELECT DISTINCT
                      Contact.ContactID, Contact.ContactFirstName, Contact.ContactLastName, Contact.ContactAddress1, Contact.ContactAddress2, Contact.ContactCity,
                      Contact.ContactState, Contact.ContactZip, Contact.ContactPhone, Contact.ContactFax, Contact.ContactEmail, Contact.CompanyRepresenting,
                      CategoryType.CategoryTypeName, Category.CategoryName, Status.StatusName, Staff.StaffFirstName + N' ' + Staff.StaffLastName AS StaffName,
                      CategoryType.CategoryTypeID, Request.RequestID, Request.StatusID, Request.CategoryID, Request.AssignedToStaffID, Contact.AddedDate,
                      Contact.AddedBy, Contact.ModifiedDate, Contact.ModifiedBy
FROM         Category INNER JOIN
                      CategoryType ON Category.CategoryTypeID = CategoryType.CategoryTypeID INNER JOIN
                      Request ON Category.CategoryID = Request.CategoryID INNER JOIN
                      Contact ON Request.ContactID = Contact.ContactID INNER JOIN
                      Status ON Request.StatusID = Status.StatusID INNER JOIN
                      Staff ON Request.AssignedToStaffID = Staff.StaffID '

set @sql = @sql + @where
exec sp_executesql @sql
Commented:
You need to use Dynamic sql for this. Something like this:
DECLARE @ContactID int
DECLARE @SQL nvarchar(max)
DECLARE @Where1 nvarchar(4000)
 
SET @ContactID = 12
SET @Where = '(Contact.ContactID = 18)'
 
SET @SQL = 'SELECT DISTINCT 
                      Contact.ContactID, Contact.ContactFirstName, Contact.ContactLastName, Contact.ContactAddress1, Contact.ContactAddress2, Contact.ContactCity, 
                      Contact.ContactState, Contact.ContactZip, Contact.ContactPhone, Contact.ContactFax, Contact.ContactEmail, Contact.CompanyRepresenting, 
                      CategoryType.CategoryTypeName, Category.CategoryName, Status.StatusName, Staff.StaffFirstName + N' ' + Staff.StaffLastName AS StaffName, 
                      CategoryType.CategoryTypeID, Request.RequestID, Request.StatusID, Request.CategoryID, Request.AssignedToStaffID, Contact.AddedDate, 
                      Contact.AddedBy, Contact.ModifiedDate, Contact.ModifiedBy
FROM         Category INNER JOIN
                      CategoryType ON Category.CategoryTypeID = CategoryType.CategoryTypeID INNER JOIN
                      Request ON Category.CategoryID = Request.CategoryID INNER JOIN
                      Contact ON Request.ContactID = Contact.ContactID INNER JOIN
                      Status ON Request.StatusID = Status.StatusID INNER JOIN
                      Staff ON Request.AssignedToStaffID = Staff.StaffID
	WHERE '
 
SET @SQL = @SQL + @Where
 
EXEC(@SQl)

Open in new window

Awarded 2008
Awarded 2008

Commented:
sp_executesql is preferred to using EXEC, as it caches the plan better.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
Variables can be used as a placeholder for data, not for parts of the sql statement.

I assume that the commented out statement would work:  --WHERE     (Contact.ContactID = @ContactID)

Not sure if you plan to have complete where statements been passed to the SP. If you did, please reconsider. Even if it would work, it would be bad design IMHO. You should pass all 9 parameters (nullable). The you could use something like
WHERE (@filterValue1 IS NULL OR column1=@filterValue1) AND (@filterValue2 IS NULL OR column2=@filterValue2) ...

Author

Commented:
Below is my final solution.  Had to fix the single quotes in the sql statement.  Thanks for the help and quick response.  

Neil Squires
DECLARE @ContactID int
DECLARE @Where1 nvarchar(4000), @Sql nvarchar(4000)
 
SET @ContactID = 12
 
SET @Where1 = 'WHERE (Contact.ContactID = 18)'
Print @Where1
 
SET @Sql = 'SELECT DISTINCT 
                      Contact.ContactID, Contact.ContactFirstName, Contact.ContactLastName, Contact.ContactAddress1, Contact.ContactAddress2, Contact.ContactCity, 
                      Contact.ContactState, Contact.ContactZip, Contact.ContactPhone, Contact.ContactFax, Contact.ContactEmail, Contact.CompanyRepresenting, 
                      CategoryType.CategoryTypeName, Category.CategoryName, Status.StatusName, Staff.StaffFirstName + N'' '' + Staff.StaffLastName AS StaffName, 
                      CategoryType.CategoryTypeID, Request.RequestID, Request.StatusID, Request.CategoryID, Request.AssignedToStaffID, Contact.AddedDate, 
                      Contact.AddedBy, Contact.ModifiedDate, Contact.ModifiedBy
FROM         Category INNER JOIN
                      CategoryType ON Category.CategoryTypeID = CategoryType.CategoryTypeID INNER JOIN
                      Request ON Category.CategoryID = Request.CategoryID INNER JOIN
                      Contact ON Request.ContactID = Contact.ContactID INNER JOIN
                      Status ON Request.StatusID = Status.StatusID INNER JOIN
                      Staff ON Request.AssignedToStaffID = Staff.StaffID '
SET @Sql = @Sql + @Where1
exec sp_executesql @Sql

Open in new window

Awarded 2008
Awarded 2008

Commented:
awesome...glad it worked.

Commented:
Cool!

Author

Commented:
Danirk2,

I am planning to build the where clause in the stored procedure, not in the webpage.   I'll post the where clause logic in a little while after I get it done.  I'm definiately planning on doing it in a "best practices" style.

Neil

Author

Commented:
Here is my current stored procedure.  It works with the ContactID, RequestID and any combination of the text fields.  I have not yet done the portion to get it working with the dropdown lists, but I wanted to get this code posted so you can get a better idea of the stored procedure.
USE [PHEP_FCS_Requests]
GO
/****** Object:  StoredProcedure [dbo].[ns_FCSRequests_GetContactsForSearch]    Script Date: 07/21/2009 13:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Squires
-- Create date: 6/4/2009
-- Description:	Retrieves the matching Contacts / Contacts 
--				with filtered requests.
-- =============================================
 
ALTER PROCEDURE [dbo].[ns_FCSRequests_GetContactsForSearch]
(
	@ContactFirstName		nvarchar(50),
	@ContactLastName		nvarchar(50),
	@ContactCity			nvarchar(50),
	@ContactZip				nvarchar(9),
	@CompanyRepresenting	nvarchar(50),
	@CategoryTypeID			int,
	@CategoryID				int,
	@AssignedToStaffID		int,
	@StatusID				int,
	@RequestID				int,
	@ContactID				int
)
AS
 
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
 
--DECLARE @RequestID int
--SET @RequestID = 37
 
IF @RequestID > 0 BEGIN
	SELECT   DISTINCT  Contact.ContactID, Contact.ContactFirstName, Contact.ContactLastName, Contact.ContactAddress1, Contact.ContactAddress2, Contact.ContactCity, 
						  Contact.ContactState, Contact.ContactZip, Contact.ContactPhone, Contact.ContactFax, Contact.ContactEmail, Contact.CompanyRepresenting, 
						  CategoryType.CategoryTypeName, Category.CategoryName, Status.StatusName, Staff.StaffFirstName + N' ' + Staff.StaffLastName AS StaffName, 
						  CategoryType.CategoryTypeID, Request.RequestID, Request.StatusID, Request.CategoryID, Request.AssignedToStaffID, Contact.AddedDate, 
						  Contact.AddedBy, Contact.ModifiedDate, Contact.ModifiedBy
	FROM         Category INNER JOIN
						  CategoryType ON Category.CategoryTypeID = CategoryType.CategoryTypeID INNER JOIN
						  Request ON Category.CategoryID = Request.CategoryID INNER JOIN
						  Contact ON Request.ContactID = Contact.ContactID INNER JOIN
						  Status ON Request.StatusID = Status.StatusID INNER JOIN
						  Staff ON Request.AssignedToStaffID = Staff.StaffID
	WHERE     (Request.RequestID = @RequestID)
	ORDER BY Contact.CompanyRepresenting
END
ELSE IF @ContactID > 0 BEGIN
	SELECT DISTINCT   ContactID, ContactFirstName, ContactLastName, ContactAddress1, ContactAddress2, ContactCity, ContactState, ContactZip, ContactPhone, ContactFax, 
						  ContactEmail, CompanyRepresenting, AddedDate, AddedBy, ModifiedDate, ModifiedBy
	FROM         Contact
	WHERE     (Contact.ContactID = @ContactID)
	ORDER BY CompanyRepresenting
END
ELSE BEGIN
	-- Build a dynamic where clause to filter the results.
 
	DECLARE @WhereClause nvarchar(4000), @Sql nvarchar(4000)
	SET @WhereClause = ''
 
	IF @ContactFirstName IS NOT NULL BEGIN
		IF LEN(@WhereClause) = 0 BEGIN
			SET @WhereClause = 'WHERE (ContactFirstName LIKE ''' + @ContactFirstName + '%'')'
		END
		ELSE BEGIN
			SET @WhereClause = @WhereClause + 'AND (ContactFirstName LIKE ''' + @ContactFirstName + '%'')'
		END
	END
 
	IF @ContactLastName IS NOT NULL BEGIN
		IF LEN(@WhereClause) = 0 BEGIN
			SET @WhereClause = 'WHERE (ContactLastName LIKE ''' + @ContactLastName + '%'')'
		END
		ELSE BEGIN
			SET @WhereClause = @WhereClause + 'AND (ContactLastName LIKE ''' + @ContactLastName + '%'')'
		END
	END
 
	IF @ContactCity IS NOT NULL BEGIN
		IF LEN(@WhereClause) = 0 BEGIN
			SET @WhereClause = 'WHERE (ContactCity LIKE ''' + @ContactCity + '%'')'
		END
		ELSE BEGIN
			SET @WhereClause = @WhereClause + 'AND (ContactCity LIKE ''' + @ContactCity + '%'')'
		END
	END
 
	IF @ContactZip IS NOT NULL BEGIN
		IF LEN(@WhereClause) = 0 BEGIN
			SET @WhereClause = 'WHERE (ContactZip LIKE ''' + @ContactZip + '%'')'
		END
		ELSE BEGIN
			SET @WhereClause = @WhereClause + 'AND (ContactZip LIKE ''' + @ContactZip + '%'')'
		END
	END
 
	IF @CompanyRepresenting IS NOT NULL BEGIN
		IF LEN(@WhereClause) = 0 BEGIN
			SET @WhereClause = 'WHERE (CompanyRepresenting LIKE ''' + @CompanyRepresenting + '%'')'
		END
		ELSE BEGIN
			SET @WhereClause = @WhereClause + 'AND (CompanyRepresenting LIKE ''' + @CompanyRepresenting + '%'')'
		END
	END
 
	SET @Sql = 'SELECT DISTINCT 
						  Contact.ContactID, Contact.ContactFirstName, Contact.ContactLastName, Contact.ContactAddress1, Contact.ContactAddress2, Contact.ContactCity, 
						  Contact.ContactState, Contact.ContactZip, Contact.ContactPhone, Contact.ContactFax, Contact.ContactEmail, Contact.CompanyRepresenting, 
						  CategoryType.CategoryTypeName, Category.CategoryName, Status.StatusName, Staff.StaffFirstName + N'' '' + Staff.StaffLastName AS StaffName, 
						  CategoryType.CategoryTypeID, Request.RequestID, Request.StatusID, Request.CategoryID, Request.AssignedToStaffID, Contact.AddedDate, 
						  Contact.AddedBy, Contact.ModifiedDate, Contact.ModifiedBy
	FROM         Category INNER JOIN
						  CategoryType ON Category.CategoryTypeID = CategoryType.CategoryTypeID INNER JOIN
						  Request ON Category.CategoryID = Request.CategoryID INNER JOIN
						  Contact ON Request.ContactID = Contact.ContactID INNER JOIN
						  Status ON Request.StatusID = Status.StatusID INNER JOIN
						  Staff ON Request.AssignedToStaffID = Staff.StaffID '
	SET @Sql = @Sql + @WhereClause
	Print @Sql
	exec sp_executesql @Sql
END

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial