Conditional search inline existing search

Transact-SQL
1

I have a big SPROC which performs a search with a CTE depending on the value of a parameter being passed into the SPROC.
The CTE is used for paging purposes (SQL Server 2008).

I am wondering if there is a way to modify the query chain so that if a 2nd parameter is passed, it is computed as an optional parameter and bring back results with that parameter in mind?

so instead of doing a check to see if the 2nd parameter is null, then do this kind of search otherwise do another kind of search - is there a way I can include the 2nd parameter in the existing query but if it is not null then to AND it?

a typical query I am performing is as follows:

WITH ProductSummary AS
	(
		SELECT p.[ID], p.Product, p.SecondName, p.Manufacturer, p.Category, p.CategoryID,
		ROW_NUMBER() OVER (ORDER BY 
		CASE @sortDir
			WHEN 'DESC' THEN NULL ELSE 1
		END ASC,		
		CASE @sortByFieldName
			WHEN 'Product' THEN p.Product
			WHEN 'CommonName' THEN p.SECONDNAME
			WHEN 'Manufacturer' THEN p.MANUFACTURER
			WHEN 'Category' THEN p.Category
		END ASC,
		CASE @sortByFieldName
			WHEN 'SDSID' THEN p.SDSID --CAST(SDSID as varchar(50))		
		END ASC) AS RowNumberAsc,
		ROW_NUMBER() OVER (ORDER BY
		CASE @sortDir
			WHEN 'ASC' THEN NULL ELSE 1 
		END DESC,	
			
		CASE @sortByFieldName			
			WHEN 'Product' THEN p.Product
			WHEN 'CommonName' THEN p.SECONDNAME
			WHEN 'Manufacturer' THEN p.MANUFACTURER
			WHEN 'Category' THEN p.Category
		END DESC,
		
		CASE @sortByFieldName 
			WHEN 'ID' THEN p.ID -- CAST(SDSID as varchar(50))
		END DESC) AS RowNumberDesc
		
		
		FROM v_ProdsSummary p
		WHERE (@columnName IS NULL AND @columnValue IS NULL)
		OR
			CASE @columnName
				WHEN 'Category' THEN p.CATEGORY
				WHEN 'Manufacturer' THEN p.MANUFACTURER
				WHEN 'Manufacturer Name' THEN p.MANUFACTURER
				WHEN 'Second Name' THEN p.SECONDNAME
				WHEN 'Common Name' THEN p.SECONDNAME
				WHEN 'Product Name' THEN p.PRODUCT				
			END
		LIKE @columnValue
	)
	
	
		SELECT 
			CASE @sortDir
				WHEN 'DESC' THEN RowNumberDesc ELSE RowNumberAsc
			END AS 
			'RowNumber', [ID], Product, SecondName, Manufacturer, Category, CategoryID, DISCONTINUED
		FROM ProductSummary 
		WHERE CASE @sortDir
			WHEN 'DESC' THEN RowNumberDesc ELSE RowNumberAsc
		END
		BETWEEN @firstRow AND @lastRow
		ORDER BY 		
			CASE @sortDir 
				WHEN 'DESC' THEN RowNumberDesc 
			ELSE 
				RowNumberAsc 
			END
			
		END

so if I add another parameter such as "secondValueCondition" then I want this to be included here (but optionally being executed):

FROM v_ProdsSummary p
		WHERE (@columnName IS NULL AND @columnValue IS NULL)
		OR
			CASE @columnName
				WHEN 'Category' THEN p.CATEGORY
				WHEN 'Manufacturer' THEN p.MANUFACTURER
				WHEN 'Manufacturer Name' THEN p.MANUFACTURER
				WHEN 'Second Name' THEN p.SECONDNAME
				WHEN 'Common Name' THEN p.SECONDNAME
				WHEN 'Product Name' THEN p.PRODUCT				
			END
		LIKE @columnValue
2

should be able to do it with a case:

...AND 1 = case
           when @secondValueCondition is null then 1
           else case @secondValueCondition 
                  when '...' then 1
                   else 0
           end
   end
3

not sure I follow your response gbritton. can you give more of an expanded answer please?

thanks

4

Hi

I am posting a sample proc here .. hope it helps you and answers your question

CREATE PROCEDURE dbo.Testing
@p1 INT=NULL, @p2 INT=NULL
AS
;
WITH   abc_cte
AS     (SELECT *
        FROM   [HumanResources].[employee]
        WHERE  businessentityid = isnull(@p1,businessentityid)
               AND nationalidnumber = Isnull(@p2, nationalidnumber))
SELECT *
FROM   abc_cte;
GO
5

thanks hhrr - I am skeptical but should hopefully work.

6

add an addition condition to your WHERE clause using AND. The sample shows you how. AND 1 = CASE ...END matches 1 with the result of the CASE expression. The first part of the CASE expression "when @secondValueCondition is null then 1" handles the case when the second value condition is not supplied. If it is supplied, the nested case statement compares the second condition (in whatever way makes sense in your case -- that's up to you) and if it passes the test, returns 1 (thus satisfying the AND 1=), If it fails the test, it returns 0, when causes the WHERE predicate to become false.

7

Note that this style of "Wildcard" doesn't work if the underlying column itself can be NULL

More common approach for that scenario is

        WHERE  (businessentityid = @p1 OR @p1 IS NULL)
               AND (nationalidnumber = @p2 OR @p2 IS NULL)

but of course performance of that can be dire ...

You can move to Dynamic SQL and construct the SQL to only include clauses in the WHERE that are needed - e.g. so if the user does not provide a value for @p1 then the test on businessentityid is left out altogether. The downside of that, in an Sproc, is that the User then needs SELECT permission on the underlying table (rather than just EXEC on the Sproc). There are workarounds including EXECUTE AS and Certificates to allow the SProc to run with different permissions. If you use sp_ExecuteSQL to execute the code AND a parametrised query, the performance will be very good (populate / frequently repeated queries will have their Query Plan cached)

Or you can have a number of separate queries and choose which one to actually run based on on some IF THEN ELSE logic. I kinda resent duplicating code in this situation, because it is more likely to lead to future-bugs when one-bit is changed and the other-bit is overlooked, but SQL will cache the query plan for each separate query so they will all perform optimally so sometimes its a case of performance-over-code-stability.

There are variations-on-a-theme available but here is a trivialised example

IF @p1 IS NOT NULL AND @p2 IS NOT NULL
        SELECT ...
        WHERE  businessentityid = @p1
               AND nationalidnumber = @p2
ELSE
IF @p1 IS NOT NULL
        SELECT ...
        WHERE  businessentityid = @p1
ELSE
IF @p2 IS NOT NULL
        SELECT ...
        WHERE  nationalidnumber = @p2
ELSE
... raiserror() ...

We tend to do "the few most popular scenarios" just getting the PKey(s) into a temporary table and then do

SELECT *
        FROM #MyTempTable AS T
             JOIN [HumanResources].[employee] AS E
                 ON E.SomeID = T.SomeID
        WHERE  (businessentityid = @p1 OR @p1 IS NULL)
               AND (nationalidnumber = @p2 OR @p2 IS NULL)

i.e. the earlier test that was chosen and which populated #MyTempTable did not (in this example) make all possible tests, just some quick tests on a key column, so the final query includes all possible tests. Likelihood is that #MyTempTable contains some rows that will not satisfy ALL the tests ... OR ... the user left most of the tests NULL and thus it WILL be a perfect 1:1 match.

Either way, the final query includes more tables, used in JOINs for Display Columns which were not required in the WHERE clause. For me, this gets more worthwhile as the complexity, and number of report-criteria form fields, grows. We typically have 50 or more Form Fields that the user can use to set the criteria for their report. Putting them all in one humongous WHERE clause guarantees a Table Scan which, on a large table, is very slow and avoids the benefit of any/every index that is available on that table :frowning: Doing a first-pass to get the PKeys into a #TempTable can work out much faster in such circumstances.

8

Hi

Or we could do

isnull(businessentityid,1) = isnull(@p1,isnull(businessentityid,1))

9

Personally I find that a lot less readable than

(businessentityid = @p1 OR @p1 IS NULL)

I don't know if my way is SARGable, but its got a lot more chance! and I can't see that complex use of IsNull is ever going to be SARGable. Plus there is also the problem of choosing a value for the IsNull that can never, not now and not ever in the future, be a valid value for the column itself. For example, I've seen "-1" used for an IDENTITY column and then, years later, when IDENTITY ran out a negative identity was set as a temporary fix instead ...

10

performs better too!

1 Like