Build conditional WHERE clause

Hi All,

I'm building a simple stored proc to be the basis of an SQL Report.

I've set up my parameters to default to NULL, as the users will have the option to filter the parameters to build the report. I have 2 datetime params which are causing me a problem.

I have a condition which checks if these 2 params are NULL, if they are, it just continues and executes the SQL query, and all is well. However, if they contain dates, I am trying to build a where condition in the variable named @.where_clause, and then I append this to the end of my SQL statement.

- If my @.where_clause is empty, the stored proc returns the desired results
- If the @.where_clause is NOT empty, my stored proc executes but returns NO results
- And last, if I modify the stored proc and remove the WHERE clause completely, and just add the "AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time". to the end of my statement in plain SQL, then it works.

** It only seems to not work when I put build my WHERE clause in a variable **

I hope this is not too confusing.

Here is the stored proc as it is now....not working.

ALTER PROCEDURE [dbo].[spReport_ProductSerialAssemblyResults]
-- Add the parameters for the stored procedure here
@.pec varchar(30) = NULL,
@.rel varchar(30) = NULL,
@.ver varchar(10) = NULL,
@.sn varchar(50) = NULL,
@.start_time datetime = NULL,
@.end_time datetime = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @.where_clause varchar(MAX)

set @.where_clause = '';

if ((@.start_time is not null) and (@.end_time is not null))
BEGIN
set @.where_clause = ' AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time';
END



-- Insert statements for procedure here
SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1) AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial) + @.where_clause;


END

To do what you are trying to do where you take a character variable and execute it as SQL code you need to use dynamic SQL. See the EXECUTE statement, and the sp_executesql stored procedure for info on dynamic SQL.

However, in your case dynamic SQL is not necessary (and it should be avoided unless necessary). What you can do in your SELECT statement is simply this

SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1)
AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial)
AND (tblProducts.start_time >= @.start_time
AND tblProducts.start_time <= @.end_time
OR (@.start_time IS NULL OR @.end_time IS NULL))

|||

Please take a look at the link below for various solutions that doesn't require dynamic SQL code.

http://www.sommarskog.se/dyn-search.html

|||Thank you both for the great feedback...