USE [CB_Gymnastics]
GO
/****** Object:  StoredProcedure [dbo].[usp_Members_SelectByFirstNameAndLastName]    Script Date: 08/02/2012 20:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Members_SelectByFirstNameAndLastName]
@i_params varchar(1000)

  as

declare @sClubID varchar(50)
declare @Member_FirstName varchar(20)
declare @Member_LastName varchar(20)
declare @Member_IsGymnast bit
declare @Member_IsOfficial bit
declare @Mode varchar(50)
declare @AndWhere varchar(1000) = ''


select @sClubID = value from dbo.fnSplit(@i_params, '|') where idx = 2
select @Member_FirstName = value from dbo.fnSplit(@i_params, '|') where idx = 3
select @Member_LastName = value from dbo.fnSplit(@i_params, '|') where idx = 4
select @Member_IsGymnast = cast(value as bit) from dbo.fnSplit(@i_params, '|') where idx = 5
select @Member_IsOfficial = cast(value as bit) from dbo.fnSplit(@i_params, '|') where idx = 6

IF @Mode = 'Gymnasts'
SET @AndWhere = @AndWhere + 'AND @Member_IsGymnast = 1'
IF @Mode = 'Officials'
SET @AndWhere = @AndWhere + 'AND @Member_IsOfficial = 1'

select 
	MemberID, Member_FirstName, Member_LastName, Member_Gender, Member_DOB, Member_BgMembershipNumber, Member_BgMembershipWith
from
    dbo.tbl_Members
where
    Member_ClubID = @sClubID AND Member_IsGymnast = @Member_IsGymnast AND Member_IsOfficial = @Member_IsOfficial
    AND Member_FirstName like '%' + @Member_FirstName + '%' 
    AND Member_LastName like '%' + @Member_LastName + '%'
    @AndWhere

where
    Member_ClubID = @sClubID AND Member_IsGymnast = @Member_IsGymnast AND Member_IsOfficial = @Member_IsOfficial
    AND Member_FirstName like '%' + @Member_FirstName + '%' 
    AND Member_LastName like '%' + @Member_LastName + '%'
    AND 
    (
    	(@Mode = 'Gymnasts' AND @Member_IsGymnast = 1)
		OR
    	(@Mode = 'Officials' AND @Member_IsOfficial = 1)
    )

quote:

---

Originally posted by visakh16

Isnt this problem solved yet?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177402

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by sunitabeck

T-SQL syntax does not let you specify part of the WHERE clause as a variable as you are attempting to do. What you will need to do is something like this:

where
    Member_ClubID = @sClubID AND Member_IsGymnast = @Member_IsGymnast AND Member_IsOfficial = @Member_IsOfficial
    AND Member_FirstName like '%' + @Member_FirstName + '%' 
    AND Member_LastName like '%' + @Member_LastName + '%'
    AND 
    (
    	(@Mode = 'Gymnasts' AND @Member_IsGymnast = 1)
		OR
    	(@Mode = 'Officials' AND @Member_IsOfficial = 1)
    )

But this may have some performance implications - see Gail Shaw's article here for an option that would perform better: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

---