how can I add to to parameter the query

query
"  and Country=BM and tbl_Profile.Gender='1' and DateDiff('yyyy', tbl_profile.Birthdate,date())>=18 and DateDiff('yyyy',tbl_profile.Birthdate,date())<=35"
-----------------------------------
Need to add the query string to stored proc.

 public static DBProfileCollection GetAllProfilesBasicSearch(int PageSize, int PageIndex, out int TotalRecords, string sqlcountry, string sqlgender, string sqlage, string sqlonlinenow)
    {
        string query = sqlcountry + sqlgender + sqlage + sqlonlinenow;
        TotalRecords = 0;

        DBProfileCollection profileCollection = new DBProfileCollection();
        Database db = OmegaLoveSqlDataHelper.CreateConnection(ConnnectionString);
        DbCommand dbCommand = db.GetStoredProcCommand("prc_Profile_Search_Basic");

        db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
        db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
        db.AddInParameter(dbCommand, "Query", DbType.String, query);
        db.AddOutParameter(dbCommand, "TotalRecords", DbType.Int32, 0);
        using (IDataReader dataReader = db.ExecuteReader(dbCommand))
        {
            while (dataReader.Read())
            {
                DBProfile profile = GetProfileFromReader(dataReader);
                profileCollection.Add(profile);
            }
        }
        TotalRecords = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalRecords"));
        return profileCollection;
    }
USE [omegalove]
GO
/****** Object:  StoredProcedure [dbo].[prc_Profile_Select_All]    Script Date: 10/16/2010 20:34:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prc_Profile_Search_Basic]
   
	@PageIndex				int = 0, 
	@PageSize				int = 2147483644,
	@TotalRecords			int = null OUTPUT
AS
BEGIN

--paging
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	
	
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)	
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1
	
	CREATE TABLE #PageIndex 
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		ProfileID int NOT NULL,
	)

	INSERT INTO #PageIndex (ProfileID)
	
	SELECT DISTINCT 
	
	a.ProfileID
	
	FROM tbl_profile a WITH (NOLOCK)
	
	ORDER BY a.ProfileID ASC

	SET @TotalRecords = @@rowcount	
	SET ROWCOUNT @RowsToReturn
	
SELECT  
a.ProfileID as ProfileID,
a.UserID as UserID,
a.UserName  as UserName,
a.HeadLine  as HeadLine,
a.EmailAddress as EmailAddress,
a.GenderID  as Gender,
b.Gender  as GenderName,
a.SeekingID as Seeking,
c.Seeking as SeekingName,
a.SearchAgeStartID as StartAge,
a.SearchAgeEndID as EndAge,
a.Birthdate as BirthDate,
dbo.fnAge_GetAgeNumber(a.Birthdate) AS Age,
dbo.fnZodiac_GetZodiacName(a.Birthdate) AS ZodiacName,
ZipCode as ZipCode,
a.CountryID as Country,
d.Country as CountryName,
a.RegionID AS Region,
dbo.fnRegions_GetRegionName(a.RegionID, d.CountryID) as RegionName,
a.CityID AS CityName,

a.AppearanceID as Appearance,
e.Appearance as AppearanceName,

a.BodyTypeID as BodyType,
f.BodyType as BodyTypeName,

a.StyleID AS Style,
g.Style AS StyleName,

a.ComplexionID AS Complexion,
h.Complexion AS ComplexionName,

a.EyeColorID AS EyeColor,
i.EyeColor AS EyeColorName,

a.EyeWearID AS EyeWear,
j.EyeWear AS EyeWearName,

a.FacialHairID AS FacialHair,
k.FacialHair AS FacialHairName,

a.FacialShapeID AS FacialShape,
l.FacialShape AS FacialShapeName,

a.FashionSenseID AS FashionSense,
m.FashionSense AS FashionSenseName,

a.BestFeatureID AS BestFeature,
n.BestFeature AS BestFeatureName,

a.HairColorID AS HairColor,
o.HairColor AS HairColorName,

a.HairStyleID AS HairStyle,
p.HairStyle AS HairStyleName,

a.HeightID AS Height,
q.Height AS HeightName,

a.WeightID AS Weight,
r.Weight AS WeightName,

a.PiercingID AS Piercing,
s.Piercing AS PiercingName,

a.TattooID AS Tattoo,
t.Tattoo AS TattooName,

a.JewelryID AS Jewelry,
u.Jewelry AS JewelryName,

a.EthnicityID AS Ethnicity,
v.Ethnicity AS EthnicityName,

a.SmokingID AS Smoking,
w.Smoking AS SmokingName,

a.DrinkingID AS Drinking,
x.Drinking AS DrinkingName,

a.DrugID AS Drug,
y.Drug AS DrugName,

a.DietID AS Diet,
z.Diet AS DietName,

a.DisabilityID AS Disability,
aa.Disability AS DisabilityName,

a.ReligionID AS Religion,
ab.Religion AS ReligionName,

a.NationalityID AS Nationality,
ac.Nationality AS NationalityName,

a.EducationID AS Education,
ad.Education AS EducationName,

a.OccupationID AS Occupation,
ae.Occupation AS OccupationName,

a.EmploymentStatusID AS EmploymentStatus,
af.EmploymentStatus AS EmploymentStatusName,

a.HaveChildrenID AS HaveChildren,
ag.HaveChildren AS HaveChildrenName,

a.WantChildrenID AS WantChildren,
ah.WantChildren AS WantChildrenName,

a.MotherTongueID AS MotherTongue,
ai.MotherTongue AS MotherTongueName,

a.IncomeID AS Income,
aj.Income AS IncomeName,

a.Latitude AS Latitude,
a.Longitude AS Longitude,
a.IPAddress AS IPAddress,

a.SenseHumorID AS SenseHumor,
ak.SenseHumor AS SenseHumorName,

a.SpendingHabitID AS SpendingHabit,
al.SpendingHabit AS SpendingHabitName,

a.RelocateID AS Relocate,
am.Relocate AS RelocateName,

a.TransportationID AS Transportation,
an.Transportation AS TransportationName,

a.StatusID AS MaritalStatus,
ao.Status AS MaritalStatusName,

a.AboutMe AS AboutMeText,
a.SearchFor AS SearchForText,
a.IdealDate AS IdealDateText,
a.FearMeeting AS FearMeetingText,
a.MeHappy AS MeHappyText,

a.Casual_Shortterm AS CasualShortTermCheck,
a.Email AS EmailCheck,
a.Friends AS FriendsCheck,
a.LongTerm AS LongTermCheck,
a.Marriage AS MarriageCheck,
a.PhysicalIntimate AS PhysicalIntimateCheck,

a.PreviousLogin AS PreviousLogin,
a.LastLogin AS LastLogin,
a.LoginCount AS LoginCount,
a.LastOnline AS LastOnline,
a.UserSince  AS UserSince,
a.LastSessionID AS LastSessionID,
a.ConfirmationGUID AS ConfirmationGUID,
a.IsOnlineNow AS IsOnlineNow

--ap.BestDescribedID AS BestDescribed,
--ap.BestDescribedText AS BestDescribedName,
--ap.CheckBox AS BestDescribedCheckBox

--aq.FoodID AS Food,
--aq.FoodText AS FoodName,
--aq.CheckBox AS FoodCheckBox,
--
--ar.HobbyID AS Hobby,
--ar.HobbyText AS HobbyName,
--ar.CheckBox AS HobbyCheckBox,
--
--at.LanguageID AS Languages,
--at.LanguageText AS LanguagesName,
--at.CheckBox AS LanguagesCheckBox,
--
--au.LifeValuesID AS LifeValues,
--au.LifeValuesText AS LifeValuesName,
--au.CheckBox AS LifeValuesCheckBox,
--
--av.LookingForID AS LookingFor,
--av.LookingForText AS LookingForName,
--av.CheckBox AS LookingForCheckBox,
--
--aw.MusicID AS Music,
--aw.MusicText AS MusicName,
--aw.CheckBox AS MusicCheckBox,
--
--ax.ReadingID AS Reading,
--ax.ReadingText AS ReadingName,
--ax.CheckBox AS ReadingCheckBox,
--
--az.SportID AS Sport,
--az.SportText AS SportName,
--az.CheckBox AS SportCheckBox

FROM 
#PageIndex [pi]
INNER JOIN  tbl_profile a on a.ProfileID = [pi].ProfileID
INNER JOIN  tbl_lookup_gender b  ON a.GenderID = b.GenderID
INNER JOIN  tbl_lookup_seeking c  ON a.SeekingID = c.SeekingID
INNER JOIN  CountryCodes d  ON a.CountryID = d.CountryID
INNER JOIN  tbl_lookup_appearance e  ON a.AppearanceID = e.AppearanceID
INNER JOIN  tbl_lookup_bodytype f  ON a.BodyTypeID = f.BodyTypeID
INNER JOIN  tbl_lookup_style g  ON a.StyleID = g.StyleID
INNER JOIN  tbl_lookup_complexion h  ON a.ComplexionID = h.ComplexionID
INNER JOIN  tbl_lookup_eyecolor i  ON a.EyeColorID = i.EyeColorID
INNER JOIN  tbl_lookup_eyewear j  ON a.EyeWearID = j.EyeWearID
INNER JOIN  tbl_lookup_facialhair k  ON a.FacialHairID = k.FacialHairID
INNER JOIN  tbl_lookup_facialshape l  ON a.FacialShapeID = l.FacialShapeID  
INNER JOIN  tbl_lookup_fashionsense m  ON a.FashionSenseID = m.FashionSenseID
INNER JOIN  tbl_lookup_bestfeature n  ON a.BestFeatureID = n.BestFeatureID  
INNER JOIN  tbl_lookup_haircolor o  ON a.HairColorID = o.HairColorID  
INNER JOIN  tbl_lookup_hairstyle p  ON a.HairStyleID = p.HairStyleID    
INNER JOIN  tbl_lookup_height q ON a.HeightID = q.HeightID  
INNER JOIN  tbl_lookup_weight r  ON a.WeightID = r.WeightID
INNER JOIN  tbl_lookup_piercing s  ON a.PiercingID = s.PiercingID
INNER JOIN  tbl_lookup_tattoo t ON a.TattooID = t.TattooID
INNER JOIN  tbl_lookup_jewelry u ON a.JewelryID = u.JewelryID
INNER JOIN  tbl_lookup_ethnicity v ON a.EthnicityID= v.EthnicityID
INNER JOIN  tbl_lookup_smoking w ON a.SmokingID= w.SmokingID
INNER JOIN  tbl_lookup_drinking x ON a.DrinkingID= x.DrinkingID
INNER JOIN  tbl_lookup_drug y ON a.DrugID= y.DrugID
INNER JOIN  tbl_lookup_diet z ON a.DietID= z.DietID
INNER JOIN  tbl_lookup_disability aa ON a.DisabilityID = aa.DisabilityID
INNER JOIN  tbl_lookup_religion ab ON a.ReligionID = ab.ReligionID
INNER JOIN  tbl_lookup_nationality ac ON a.NationalityID = ac.NationalityID
INNER JOIN  tbl_lookup_education ad ON a.EducationID = ad.EducationID
INNER JOIN  tbl_lookup_occupation ae ON a.OccupationID = ae.OccupationID
INNER JOIN  tbl_lookup_employmentstatus af ON a.EmploymentStatusID = af.EmploymentStatusID
INNER JOIN  tbl_lookup_havechildren ag ON a.HaveChildrenID = ag.HaveChildrenID
INNER JOIN  tbl_lookup_wantchildren ah ON a.WantChildrenID = ah.WantChildrenID
INNER JOIN  tbl_lookup_mothertongue ai ON a.MotherTongueID = ai.MotherTongueID
INNER JOIN  tbl_lookup_income aj ON a.IncomeID = aj.IncomeID
INNER JOIN  tbl_lookup_sensehumor ak ON a.SenseHumorID = ak.SenseHumorID
INNER JOIN  tbl_lookup_spendinghabit al ON a.SpendingHabitID = al.SpendingHabitID
INNER JOIN  tbl_lookup_relocates am ON a.RelocateID = am.RelocateID
INNER JOIN  tbl_lookup_transportation an ON a.TransportationID = an.TransportationID
INNER JOIN  tbl_lookup_status ao ON a.StatusID = ao.StatusID
--OUTER APPLY fnBestDescribed_SelectBestDescribedByProfileID(a.ProfileID) ap
--OUTER APPLY fnFood_SelectFoodByProfileID(a.ProfileID) aq
--OUTER APPLY fnHobby_SelectHobbyByProfileID(a.ProfileID) ar
--OUTER APPLY fnLanguage_SelectLanguageByProfileID(a.ProfileID) at
--OUTER APPLY fnLifeValues_SelectLifeValuesByProfileID(a.ProfileID) au
--OUTER APPLY fnLookingFor_SelectLookingForByProfileID(a.ProfileID) av
--OUTER APPLY fnMusic_SelectMusicByProfileID(a.ProfileID) aw
--OUTER APPLY fnReading_SelectReadingByProfileID(a.ProfileID) ax
--OUTER APPLY fnSport_SelectSportByProfileID(a.ProfileID) az
WHERE
		[pi].IndexID > @PageLowerBound AND 
		[pi].IndexID < @PageUpperBound
	ORDER BY
		IndexID
	
	SET ROWCOUNT 0

	
	
END

Open in new window

omegaloveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AmmarRCommented:
Dear omegalove

it normally doesn't work this way, i mean you cannot send part of a query to a store procedure, it wont work
--

but there is a work around.

and your query is a completion of a where clause

query
"  and Country=BM and tbl_Profile.Gender='1' and DateDiff('yyyy', tbl_profile.Birthdate,date())>=18 and DateDiff('yyyy',tbl_profile.Birthdate,date())<=35"

if you want to send a query to an sp or function what you can do is the following in the code snippet below

you create your query and then execute it using the exec command

hope this example clears
Regards
ALTER PROCEDURE [dbo].[prc_Profile_Search_Basic]
         @addedQuery varchar(500)
AS
BEGIN

Declare @MainQry varchar(500)

set @MainQry = 'select * from table'

Set @MainQry = @MainQry + ' ' + @addedQuery

Exec(@MainQry)
	
END

Open in new window

omegaloveAuthor Commented:
I change it to be safe now I need to fix the attach query for the where clause

 public static DBProfileCollection GetAllProfilesBasicSearch(int PageSize, int PageIndex, out int TotalRecords, string sqlcountry, int sqlgender, int sqlage1, int sqlage2, bool sqlonlinenow)
    {
       
        TotalRecords = 0;

        DBProfileCollection profileCollection = new DBProfileCollection();
        Database db = OmegaLoveSqlDataHelper.CreateConnection(ConnnectionString);
        DbCommand dbCommand = db.GetStoredProcCommand("prc_Profile_Search_Basic");

        db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
        db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
        db.AddInParameter(dbCommand, "Country", DbType.String, sqlcountry);
        db.AddInParameter(dbCommand, "Gender", DbType.Int32, sqlgender);
        db.AddInParameter(dbCommand, "Age1", DbType.Int32, sqlage1);
        db.AddInParameter(dbCommand, "Age2", DbType.Int32, sqlage2);
        db.AddInParameter(dbCommand, "IsOnLineNow", DbType.Boolean, sqlonlinenow);


        db.AddOutParameter(dbCommand, "TotalRecords", DbType.Int32, 0);
        using (IDataReader dataReader = db.ExecuteReader(dbCommand))
        {
            while (dataReader.Read())
            {
                DBProfile profile = GetProfileFromReader(dataReader);
                profileCollection.Add(profile);
            }
        }
        TotalRecords = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalRecords"));
        return profileCollection;
    }

where a where clause is not needed.
USE [omegalove]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[prc_Profile_Search_Basic]
	@PageIndex				int = 0, 
	@PageSize				int = 2147483644,
	@Country				char(2) = null, --- Country Code Example CA is Canada
	@Gender					int = 0, --- 1 for male and 2 for female
	@Age1					int = 0,
	@Age2					int = 0,  --- Greater than Age1
	@IsOnlineNow			bit = false, -- false implies not online.
	@TotalRecords			int = null OUTPUT
AS
BEGIN

--paging
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	
	
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)	
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1
	
	CREATE TABLE #PageIndex 
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		ProfileID int NOT NULL,
	)

	INSERT INTO #PageIndex (ProfileID)
	
	SELECT DISTINCT 
	
	a.ProfileID
	
	FROM tbl_profile a WITH (NOLOCK)
	
	ORDER BY a.ProfileID ASC

	SET @TotalRecords = @@rowcount	
	SET ROWCOUNT @RowsToReturn
	
SELECT  
a.ProfileID as ProfileID,
a.UserID as UserID,
a.UserName  as UserName,
a.HeadLine  as HeadLine,
a.EmailAddress as EmailAddress,
a.GenderID  as Gender,
b.Gender  as GenderName,
a.SeekingID as Seeking,
c.Seeking as SeekingName,
a.SearchAgeStartID as StartAge,
a.SearchAgeEndID as EndAge,
a.Birthdate as BirthDate,
dbo.fnAge_GetAgeNumber(a.Birthdate) AS Age,
dbo.fnZodiac_GetZodiacName(a.Birthdate) AS ZodiacName,
ZipCode as ZipCode,
a.CountryID as Country,
d.Country as CountryName,
a.RegionID AS Region,
dbo.fnRegions_GetRegionName(a.RegionID, d.CountryID) as RegionName,
a.CityID AS CityName,

a.AppearanceID as Appearance,
e.Appearance as AppearanceName,

a.BodyTypeID as BodyType,
f.BodyType as BodyTypeName,

a.StyleID AS Style,
g.Style AS StyleName,

a.ComplexionID AS Complexion,
h.Complexion AS ComplexionName,

a.EyeColorID AS EyeColor,
i.EyeColor AS EyeColorName,

a.EyeWearID AS EyeWear,
j.EyeWear AS EyeWearName,

a.FacialHairID AS FacialHair,
k.FacialHair AS FacialHairName,

a.FacialShapeID AS FacialShape,
l.FacialShape AS FacialShapeName,

a.FashionSenseID AS FashionSense,
m.FashionSense AS FashionSenseName,

a.BestFeatureID AS BestFeature,
n.BestFeature AS BestFeatureName,

a.HairColorID AS HairColor,
o.HairColor AS HairColorName,

a.HairStyleID AS HairStyle,
p.HairStyle AS HairStyleName,

a.HeightID AS Height,
q.Height AS HeightName,

a.WeightID AS Weight,
r.Weight AS WeightName,

a.PiercingID AS Piercing,
s.Piercing AS PiercingName,

a.TattooID AS Tattoo,
t.Tattoo AS TattooName,

a.JewelryID AS Jewelry,
u.Jewelry AS JewelryName,

a.EthnicityID AS Ethnicity,
v.Ethnicity AS EthnicityName,

a.SmokingID AS Smoking,
w.Smoking AS SmokingName,

a.DrinkingID AS Drinking,
x.Drinking AS DrinkingName,

a.DrugID AS Drug,
y.Drug AS DrugName,

a.DietID AS Diet,
z.Diet AS DietName,

a.DisabilityID AS Disability,
aa.Disability AS DisabilityName,

a.ReligionID AS Religion,
ab.Religion AS ReligionName,

a.NationalityID AS Nationality,
ac.Nationality AS NationalityName,

a.EducationID AS Education,
ad.Education AS EducationName,

a.OccupationID AS Occupation,
ae.Occupation AS OccupationName,

a.EmploymentStatusID AS EmploymentStatus,
af.EmploymentStatus AS EmploymentStatusName,

a.HaveChildrenID AS HaveChildren,
ag.HaveChildren AS HaveChildrenName,

a.WantChildrenID AS WantChildren,
ah.WantChildren AS WantChildrenName,

a.MotherTongueID AS MotherTongue,
ai.MotherTongue AS MotherTongueName,

a.IncomeID AS Income,
aj.Income AS IncomeName,

a.Latitude AS Latitude,
a.Longitude AS Longitude,
a.IPAddress AS IPAddress,

a.SenseHumorID AS SenseHumor,
ak.SenseHumor AS SenseHumorName,

a.SpendingHabitID AS SpendingHabit,
al.SpendingHabit AS SpendingHabitName,

a.RelocateID AS Relocate,
am.Relocate AS RelocateName,

a.TransportationID AS Transportation,
an.Transportation AS TransportationName,

a.StatusID AS MaritalStatus,
ao.Status AS MaritalStatusName,

a.AboutMe AS AboutMeText,
a.SearchFor AS SearchForText,
a.IdealDate AS IdealDateText,
a.FearMeeting AS FearMeetingText,
a.MeHappy AS MeHappyText,

a.Casual_Shortterm AS CasualShortTermCheck,
a.Email AS EmailCheck,
a.Friends AS FriendsCheck,
a.LongTerm AS LongTermCheck,
a.Marriage AS MarriageCheck,
a.PhysicalIntimate AS PhysicalIntimateCheck,

a.PreviousLogin AS PreviousLogin,
a.LastLogin AS LastLogin,
a.LoginCount AS LoginCount,
a.LastOnline AS LastOnline,
a.UserSince  AS UserSince,
a.LastSessionID AS LastSessionID,
a.ConfirmationGUID AS ConfirmationGUID,
a.IsOnlineNow AS IsOnlineNow

--ap.BestDescribedID AS BestDescribed,
--ap.BestDescribedText AS BestDescribedName,
--ap.CheckBox AS BestDescribedCheckBox

--aq.FoodID AS Food,
--aq.FoodText AS FoodName,
--aq.CheckBox AS FoodCheckBox,
--
--ar.HobbyID AS Hobby,
--ar.HobbyText AS HobbyName,
--ar.CheckBox AS HobbyCheckBox,
--
--at.LanguageID AS Languages,
--at.LanguageText AS LanguagesName,
--at.CheckBox AS LanguagesCheckBox,
--
--au.LifeValuesID AS LifeValues,
--au.LifeValuesText AS LifeValuesName,
--au.CheckBox AS LifeValuesCheckBox,
--
--av.LookingForID AS LookingFor,
--av.LookingForText AS LookingForName,
--av.CheckBox AS LookingForCheckBox,
--
--aw.MusicID AS Music,
--aw.MusicText AS MusicName,
--aw.CheckBox AS MusicCheckBox,
--
--ax.ReadingID AS Reading,
--ax.ReadingText AS ReadingName,
--ax.CheckBox AS ReadingCheckBox,
--
--az.SportID AS Sport,
--az.SportText AS SportName,
--az.CheckBox AS SportCheckBox

FROM 
#PageIndex [pi]
INNER JOIN  tbl_profile a on a.ProfileID = [pi].ProfileID
INNER JOIN  tbl_lookup_gender b  ON a.GenderID = b.GenderID
INNER JOIN  tbl_lookup_seeking c  ON a.SeekingID = c.SeekingID
INNER JOIN  CountryCodes d  ON a.CountryID = d.CountryID
INNER JOIN  tbl_lookup_appearance e  ON a.AppearanceID = e.AppearanceID
INNER JOIN  tbl_lookup_bodytype f  ON a.BodyTypeID = f.BodyTypeID
INNER JOIN  tbl_lookup_style g  ON a.StyleID = g.StyleID
INNER JOIN  tbl_lookup_complexion h  ON a.ComplexionID = h.ComplexionID
INNER JOIN  tbl_lookup_eyecolor i  ON a.EyeColorID = i.EyeColorID
INNER JOIN  tbl_lookup_eyewear j  ON a.EyeWearID = j.EyeWearID
INNER JOIN  tbl_lookup_facialhair k  ON a.FacialHairID = k.FacialHairID
INNER JOIN  tbl_lookup_facialshape l  ON a.FacialShapeID = l.FacialShapeID  
INNER JOIN  tbl_lookup_fashionsense m  ON a.FashionSenseID = m.FashionSenseID
INNER JOIN  tbl_lookup_bestfeature n  ON a.BestFeatureID = n.BestFeatureID  
INNER JOIN  tbl_lookup_haircolor o  ON a.HairColorID = o.HairColorID  
INNER JOIN  tbl_lookup_hairstyle p  ON a.HairStyleID = p.HairStyleID    
INNER JOIN  tbl_lookup_height q ON a.HeightID = q.HeightID  
INNER JOIN  tbl_lookup_weight r  ON a.WeightID = r.WeightID
INNER JOIN  tbl_lookup_piercing s  ON a.PiercingID = s.PiercingID
INNER JOIN  tbl_lookup_tattoo t ON a.TattooID = t.TattooID
INNER JOIN  tbl_lookup_jewelry u ON a.JewelryID = u.JewelryID
INNER JOIN  tbl_lookup_ethnicity v ON a.EthnicityID= v.EthnicityID
INNER JOIN  tbl_lookup_smoking w ON a.SmokingID= w.SmokingID
INNER JOIN  tbl_lookup_drinking x ON a.DrinkingID= x.DrinkingID
INNER JOIN  tbl_lookup_drug y ON a.DrugID= y.DrugID
INNER JOIN  tbl_lookup_diet z ON a.DietID= z.DietID
INNER JOIN  tbl_lookup_disability aa ON a.DisabilityID = aa.DisabilityID
INNER JOIN  tbl_lookup_religion ab ON a.ReligionID = ab.ReligionID
INNER JOIN  tbl_lookup_nationality ac ON a.NationalityID = ac.NationalityID
INNER JOIN  tbl_lookup_education ad ON a.EducationID = ad.EducationID
INNER JOIN  tbl_lookup_occupation ae ON a.OccupationID = ae.OccupationID
INNER JOIN  tbl_lookup_employmentstatus af ON a.EmploymentStatusID = af.EmploymentStatusID
INNER JOIN  tbl_lookup_havechildren ag ON a.HaveChildrenID = ag.HaveChildrenID
INNER JOIN  tbl_lookup_wantchildren ah ON a.WantChildrenID = ah.WantChildrenID
INNER JOIN  tbl_lookup_mothertongue ai ON a.MotherTongueID = ai.MotherTongueID
INNER JOIN  tbl_lookup_income aj ON a.IncomeID = aj.IncomeID
INNER JOIN  tbl_lookup_sensehumor ak ON a.SenseHumorID = ak.SenseHumorID
INNER JOIN  tbl_lookup_spendinghabit al ON a.SpendingHabitID = al.SpendingHabitID
INNER JOIN  tbl_lookup_relocates am ON a.RelocateID = am.RelocateID
INNER JOIN  tbl_lookup_transportation an ON a.TransportationID = an.TransportationID
INNER JOIN  tbl_lookup_status ao ON a.StatusID = ao.StatusID
--OUTER APPLY fnBestDescribed_SelectBestDescribedByProfileID(a.ProfileID) ap
--OUTER APPLY fnFood_SelectFoodByProfileID(a.ProfileID) aq
--OUTER APPLY fnHobby_SelectHobbyByProfileID(a.ProfileID) ar
--OUTER APPLY fnLanguage_SelectLanguageByProfileID(a.ProfileID) at
--OUTER APPLY fnLifeValues_SelectLifeValuesByProfileID(a.ProfileID) au
--OUTER APPLY fnLookingFor_SelectLookingForByProfileID(a.ProfileID) av
--OUTER APPLY fnMusic_SelectMusicByProfileID(a.ProfileID) aw
--OUTER APPLY fnReading_SelectReadingByProfileID(a.ProfileID) ax
--OUTER APPLY fnSport_SelectSportByProfileID(a.ProfileID) az
WHERE
		[pi].IndexID > @PageLowerBound AND 
		[pi].IndexID < @PageUpperBound
	ORDER BY
		IndexID
	
	SET ROWCOUNT 0

	
	
END

Open in new window

AmmarRCommented:
you can just add it as is and use the parameters in place of the values,

i dont really know what you are up to, but i hope i am understand right

check code below

WHERE
[pi].IndexID > @PageLowerBound 
AND[pi].IndexID < @PageUpperBoundand 
And Country = @Country and 
tbl_Profile.Gender = @Gender 
and DateDiff('yyyy', tbl_profile.Birthdate,date())>= @Age1 
and DateDiff('yyyy',tbl_profile.Birthdate,date())<= @Age2

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omegaloveAuthor Commented:
k
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.