-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
--
-- For better formatting, I use 4 characters instead of 8 - for tabs.
-- See Tools (menu), Options (dialog), Editor (tab)
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET STATISTICS IO ON
-- Turn Graphical Showplan ON (Ctrl+K)

USE CREDIT
go

-- Update a rows to later search on...
UPDATE dbo.member
	SET firstname = 'Katie'
	WHERE member_no = 8234
go


IF OBJECTPROPERTY(object_id('GetMemberInfoParam'), 'IsProcedure') = 1
	DROP PROC dbo.GetMemberInfoParam
go

CREATE PROC dbo.GetMemberInfoParam
(
	@Lastname	varchar(30) = NULL,
	@Firstname	varchar(30) = NULL,
	@member_no	int = NULL
)
AS
SET NOCOUNT ON

SELECT m.* 
FROM dbo.member AS m
WHERE (m.lastname LIKE @lastname OR @lastname IS NULL)
	AND (m.member_no = @member_no OR @member_no IS NULL)
	AND (m.firstname LIKE @firstname OR @firstname IS NULL)
go

---------------------------------------------------------------
-- Make sure to look at the plan of execution AND the I/Os. 
-- Both of these will run FAST but what's wrong......
EXEC dbo.GetMemberInfoParam	
		@Lastname = 'Tripp' WITH RECOMPILE
go

EXEC dbo.GetMemberInfoParam	
		@Firstname = 'Katie' WITH RECOMPILE
go

EXEC dbo.GetMemberInfoParam	
		@Member_no = 9912 WITH RECOMPILE
go

-- What's wrong is that all of these conditions were ANDed together
-- and since AND progressively limits the search the QP said...
-- "I only need to look for something which is *selective*."
-- Well, the most selective criteria in this query is Member_no
-- so that's the best choice for an index. So - no matter which 
-- parameter(s) you supply you will always use the member_no
-- index... Which is HORRIBLE if/when you don't pass in member_no.
---------------------------------------------------------------

IF OBJECTPROPERTY(object_id('GetMemberInfoParamDSE'), 'IsProcedure') = 1
	DROP PROC dbo.GetMemberInfoParamDSE
go

CREATE PROC dbo.GetMemberInfoParamDSE
(
	@Lastname	varchar(30) = NULL,
	@Firstname	varchar(30) = NULL,
	@member_no	int = NULL
)
AS
SET NOCOUNT ON

IF @LastName IS NULL AND @FirstName IS NULL AND @Member_no IS NULL
	RAISERROR ('You must supply at least one parameter.', 16, -1)

DECLARE @ExecStr	varchar(1000)
		, @MemberNoStr	varchar(100)

SELECT @ExecStr = 'SELECT m.* FROM dbo.member AS m WHERE ' 

IF @LastName IS NOT NULL
	SELECT @Lastname = 'm.lastname LIKE ' + QUOTENAME(@lastname, '''')
IF @FirstName IS NOT NULL
	SELECT @Firstname = 'm.firstname LIKE ' + QUOTENAME(@firstname, '''')
IF @Member_no IS NOT NULL
	SELECT @MemberNoStr = 'm.member_no = ' + convert(varchar(5), @member_no)

SELECT @ExecStr = @ExecStr + ISNULL(@LastName, ' ') 
	+ 
	CASE
		WHEN @LastName IS NOT NULL AND @FirstName IS NOT NULL
			THEN ' AND '
		ELSE ' '
	END
	+
	ISNULL(@FirstName, ' ') 
	+ 
	CASE
		WHEN (@LastName IS NOT NULL OR @FirstName IS NOT NULL)
					AND @MemberNoStr IS NOT NULL
			THEN ' AND '
		ELSE ' '
	END
	+
	ISNULL(@MemberNoStr, ' ')

EXEC(@ExecStr)
go

EXEC dbo.GetMemberInfoParamDSE
			@Lastname = 'test'
			, @FirstName = 'Katie' 
go

EXEC dbo.GetMemberInfoParamDSE	
			@Firstname = 'Katie' 
go

EXEC dbo.GetMemberInfoParamDSE	
			@Firstname = 'Katie'
			, @Member_no = 842 
go

EXEC dbo.GetMemberInfoParamDSE	
			@Member_no = 9912 
go

EXEC dbo.GetMemberInfoParamDSE	
			@Lastname = 'Florini'
			, @Member_no = 9912 
go

-- And if you have better index choices..... well, the above 
-- would have benefited from those as well. Add these
-- and run the above again.

CREATE INDEX MemberLastNameInd ON Member(LastName)
CREATE INDEX MemberFirstNameInd ON Member(FirstName)

-- but there's A LOT more to appropriate indexing than
-- just creating an index for every SARG (search argument)
-- Check out the msdn webcast I did recently. Review this
-- blog entry for a complete list of everything:
-- http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=ecdcd11b-9eb7-4d88-b20c-b9225990080a
-- Hope this helps! 
-- Have fun,
-- kt