What is an efficient way to search a table for specified column values in a SQL Server stored procedure?

I 开发者_StackOverflow中文版have a stored procedure that is used for "searching" a table.

I want an exact match for the fields that are specified.

I would like to know what ways I can efficiently implement this functionality!

Here is an example of my initial attempt. I created a compound WHERE clause:

CREATE PROCEDURE usp_TableA_Search
    @ColumnA int = null,
    @ColumnB uniqueidentifier = null
AS
    SELECT *
    FROM TableA
    WHERE
        (@ColumnA IS NULL OR @ColumnA = ColumnA)
        AND
        (@ColumnB IS NULL OR @ColumnB = ColumnB)

This method feels very inefficient to me. I'm not sure if SQL will repeatedly check @ColumnA and @ColumnB for NULL? And I've heard that SQL will create an execution plan, that might not work as well when there's variable paths like this.

Is there a better way to construct this sproc?

Also, what resources can I use to determine the efficiency of my SQL code?

Something like this is done pretty well with Dynamic SQL, as long as you do it right.

SQLDenis wrote a good blog post about this.