Implications for sql 'OR' conditions when one alternative is trivial?

Question

I am creating a stored procedure to search for some data in my database according to some criteria entered by the user.

My sql code looks like this:

Create Procedure mySearchProc
(
@IDCriteria bigint=null,
...
@MaxDateCriteria datetime=null
)
as
select Col1,...,Coln from MyTable 
where (@IDCriteria is null or ID=@IDCriteria)
...
and (@MaxDateCriteria is null or Date<@MaxDateCriteria)

Edit : I have about 20 possible parameters, and each combination of n non-zero parameters can occur.

Is it good to work with this code? (I am using MS SQL Server 2008)

Will generate SQL code containing only the necessary ones, where the sentences will be noticeably faster?

Answer 1

where (@IDCriteria is null or ID=@IDCriteria)
  and (@MaxDateCriteria is null or Date<@MaxDateCriteria)

, SQL- , .

IF, .

IF @IDCriteria is not null and @MaxDateCriteria is not null

  --query
  WHERE ID = @IDCriteria and Date < @MaxDateCriteria

ELSE IF @IDCriteria is not null

  --query
  WHERE ID = @IDCriteria

ELSE IF @MaxDateCriteria is not null

  --query
  WHERE Date < @MaxDateCriteria

ELSE

  --query
  WHERE 1 = 1

, , , .

SQL-, , ?

- , .

---

Edit:

DECLARE @CustomerNumber int, @CustomerName varchar(30)

SET @CustomerNumber = 123
SET @CustomerName = '123'

SELECT * FROM Customers
WHERE (CustomerNumber = @CustomerNumber OR @CustomerNumber is null)
  AND (CustomerName = @CustomerName OR @CustomerName is null)

CustomerName CustomerNumber . : "Clustered   ". .

---

: 20 , n .

"" . , 99,9% AccountIdentifier. , , . 2 3 .

. OR /, .

Answer 2

OR, , , . OR, .

Answer 3

, :

, IF ELSE, ELSE, , .

, , , IF EL ELSE, .

Answer 4

" SQL-, , ?"

, .

Answer 5

/ ( ..) PK

(, !)

, WHERE

SELECT ...
FROM @TempTable AS T
    JOIN dbo.MyTable AS M
        ON M.ID = T.ID
WHERE (@IDCriteria IS NULL OR M.ID=@IDCriteria)
...
AND (@MaxDateCriteria IS NULL OR M.Date<@MaxDateCriteria)

() .

Answer 6

, :

WHERE (@IDCriteria IS NULL OR @IDCriteria=ID)
AND (@MaxDateCriteria IS NULL OR Date<@MaxDateCriteria)
AND ...

:

WHERE ID = ISNULL(@IDCriteria, ID)
AND Date < ISNULL(@MaxDateCriteria, DATEADD(millisecond, 1, Date))
AND ...

?

Answer 7

. IMO, , SQL .

Answer 8

, sproc . , . , sql. SQL Injection.

Answer 9

, , - , . , , .

, , , , ( ). , , , , .

: ; " "; .

, , , ( ) .

" ". , , , Query By Example, , . , , , IMHO.

Answer 10

SQL 2005, , 2008 -. , ...

• , WITH (RECOMPILE)

• CASE, . , 2005 OR. :

.

SELECT
     ...
FROM
     ...
WHERE
     (1 =
          CASE
               WHEN @my_column IS NULL THEN 1
               WHEN my_column = @my_column THEN 1
               ELSE 0
          END
     )

CASE SQL Server , . , non-null , . , .

Answer 11

, sql. .

DECLARE @sql nvarchar(4000);
SET @sql = N''

IF @param1 IS NOT NULL
    SET @sql = CASE WHEN @sql = N'' THEN N'' ELSE N' AND ' END + N'param1 = @param1';
IF @param2 IS NOT NULL
    SET @sql = CASE WHEN @sql = N'' THEN N'' ELSE N' AND ' END + N'param2 = @param2';
...
IF @paramN IS NOT NULL
    SET @sql = CASE WHEN @sql = N'' THEN N'' ELSE N' AND ' END + N'paramN = @paramN';

IF @sql <> N''
    SET @sql = N' WHERE ' + @sql;
SET @sql = N'SELECT ... FROM myTable' + @sql;

EXEC sp_executesql @sql, N'@param1 type, @param2 type, ..., @paramN type', @param1, @param2, ..., @paramN;

Answer 12

, , , . , SQL ( ) .

:

• SP (.. ), .
• SP (, ), WITH RECOMPILE, , .

OR NULL . RECOMPILE, , .