Invalid column name 'Gender'.

(19 row(s) affected)
Msg 207, Level 16, State 1, Procedure prc_Profile_Search_Basic, Line 42
Invalid column name 'Country'.
Msg 207, Level 16, State 1, Procedure prc_Profile_Search_Basic, Line 42
Invalid column name 'Gender'.

(1 row(s) affected)

(1 row(s) affected)
-------------------------------------------------------------------------

USE [omegalove]
GO

DECLARE      @return_value int,
            @TotalRecords int

SELECT      @TotalRecords = 0

EXEC      @return_value = [dbo].[prc_Profile_Search_Basic]
            @PageIndex = 2147483646,
            @PageSize = 0,
            @Country = N'DZ',
            @Gender = 1,
            @Age1 = 18,
            @Age2 = 35,
            @IsOnlineNow = false,
            @TotalRecords = @TotalRecords OUTPUT

SELECT      @TotalRecords as N'@TotalRecords'

SELECT      'Return Value' = @return_value

GO

USE [omegalove]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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

And a.Country = @Country and 
a.Gender = @Gender 
and (DateDiff(YEAR, a.BirthDate,getdate()) >= @Age1) 
and (DateDiff(YEAR,a.BirthDate,getdate()) <= @Age2)

	ORDER BY
		IndexID
	
	SET ROWCOUNT 0

	
	
END

Select allOpen in new window