
use credit
go

-- Create a small number of rows where lastname is 'Beauchemin'
UPDATE dbo.member
	SET lastname = 'Beauchemin'
		WHERE member_no IN (1234, 5678, 9876)
go

-- make sure there is an index on last name
CREATE INDEX MemberLastName ON dbo.Member(lastname)
go

create procedure GetMemberInfoSimple
(
	@MemberName		varchar(30)
)
AS
SELECT member_no, firstname, lastname, phone_no, region_no
	FROM dbo.member
	WHERE lastname LIKE @MemberName 
GO

EXEC GetMemberInfoSimple 'Beauchemin' WITH RECOMPILE -- Bookmark lookup
EXEC GetMemberInfoSimple 'B%' WITH RECOMPILE -- Clustered index scan

-- clear caches
CHECKPOINT				--writes dirty pages to disk
DBCC FREEPROCCACHE		--clears entire plan cache
DBCC DROPCLEANBUFFERS	--clears all clean data cache pages

-- Conditional
IF OBJECTPROPERTY(object_id('dbo.GetMemberNo'), 'IsProcedure') = 1
	DROP PROCEDURE dbo.GetMemberNo
go
CREATE PROCEDURE dbo.GetMemberNo
(
	@MemberName		varchar(30)
)
AS
IF @MemberName LIKE '%[%]%'
BEGIN
	PRINT 'Using the first select'
	SELECT member_no, firstname, lastname, phone_no, region_no
		FROM dbo.member
		WHERE lastname LIKE @MemberName 
END
ELSE
BEGIN
	PRINT 'Using the second select'
	SELECT member_no, firstname, lastname, phone_no, region_no
		FROM dbo.member
		WHERE lastname = @MemberName 
END
go

-- The following execution should execute the SECOND select 
-- and therefore the FIRST should not be optimized?
EXEC dbo.GetMemberNo 'Beauchemin'

-- This execution should generate the plan for the FIRST
-- select... Yet it doesn't. In the previous execution (even though 
-- the FIRST select was NOT executed) the first select was optimized
-- using the parameters targeted for the ELSE condition...
EXEC dbo.GetMemberNo 'B%'




