operator i bet you never heard about

I can bet that you never heard about this operator on c# development but it's nice to know anyway:

operator =&

usually used in logical statement which is equivalent to x&=y -------> x& (y=x)

The left-shift operator (<<) 

shifts its first operand left by the number of bits specified by its second operand (always int )

exemple : i << 1---------->0x2

Operator >>= 
is equivalent to x >>= y---------> x = x >> y


Operator  x /= y
is equivalent to x /= y---------> x = x / y

Dynamically changing searches based on the given parameters

It is very common in information systems to have functions where the users are able to search the data by selecting freely among many possible criterias. When you implement such a function with SQL Server there are two challenges: to produce the correct result and have good performance. Here few option i found:

Option 1: Using IF statements

IF @ClientNum IS NOT NULL
SELECT ... FROM CLients WHERE ClientNum = @ClientNum
ELSE IF @NumMatric IS NOT NULL
SELECT ... FROM Client WHERE NumMatric= @NumMatric
ELSE IF @Clientname IS NOT NULL
SELECT TOP 100// This is to have only few client and not all of them
FROM Client
WHERE clientName LIKE @clientName + '%'
ORDER BY clientName
ELSE
RAISERROR('No client found!')

Option 2:Using OR

If you don't like the multiple IF statements:

SELECT TOP 100
FROM Client
WHERE (ClientNum = @ClientNum AND @ClientNum IS NOT NULL) OR
(NumMatric = @NumMatric AND @NumMatric IS NOT NULL) OR
(Clientname LIKE @Clientname + '%' AND @Clientname IS NOT NULL)
ORDER BY Clientname

Option 3: Using Dynamic SQL

Here we assuming that OPTION (RECOMPILE) works like it does in SQL 2008 SP2, SQL 2008 R2
CREATE PROCEDURE ClSearch
@ClientName varchar(25) = null,
@ClientNum varchar(25) = null,
@ClientMatrix varchar(25) = null
AS
BEGIN
SELECT *
FROM Client
WHERE
(@ClientName IS NULL OR (ClientName = @ClientName ))
AND (@ClientNum IS NULL OR (ClientNum = @ClientNum ))
AND (@ClientMatrix IS NULL OR (ClientMatrix = @ClientMatrix ))
OPTION (RECOMPILE)
END

PS: Since the query is recompiled every time, you get a query plan that is optimised for the exact search conditions at hand. But if the search routine is called with high frequency, the recurring compilation may cause an overload on the server.

Handling Multi-valued Parameters:

To handel Multi-valued parametre the best way is to use intlist_to_tbl is a function  that cracks a comma-separated list into table ex:
AND (ClientMatrix IN (SELECT MatrixTabClient FROM intlist_to_tbl(@ClientMatrix))
OR @ClientMatrix IS NULL)


In the end i want to say changing searches based on the given parameters is a complicated subject and i always recommend to use index this will gain a lot of performance in the result.

Reference:
Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog