Help with the SQL View

I have written the following procedure which is used to query a VIEW based on the Input parameters. If any one of the input parameter passed to this procedure is -1, then i do not want that corresponding column in the Where clause to be executed. (ie if @Weight is -1 then in the where clause WEIGHT column should not be used to filter the search results.

I have one record with the country set as 252 (ie USA) and when that is the only input parameter passed with a value other than -1, i should be seeing the record, but i do not see the same. Can anyone help me find an issue with the procedure i have written and if there is a better way to implement, please do let me know.

Also one another question, i want to Use the @AGE1, @AGE2 , to compare if the value Column  AGE lies between @AGE1 and @AGE2 and if so those records should be retrieved . Just add this criteria to the current procedure.


PROCEDURE [dbo].[DRM_ProfileSearch_RetrieveProfiles]
(
@AGE1_ID int,
@AGE2_ID int,
@HEIGHT_ID int,
@WEIGHT_ID int,
@RELIGION_ID int,
@GENDER_ID int,
@CASTE_ID int,
@MARITIAL_STATUS_ID int,
@EDUCATION_ID int,
@STATE_ID int,
@COUNTRY_ID int,
@PROFESSION_ID int,
@CITIZENSHIP_ID int,
@IS_PHOTO_ID int,
@SEEKING_ID int,
@SEARCH_QUERY nvarchar(1000)
)
AS

SET NOCOUNT OFF;
 
 IF @AGE1_ID = -1   SET @AGE1_ID = NULL
IF @AGE2_ID = -1   SET @AGE2_ID = NULL
IF @HEIGHT_ID = -1   SET @HEIGHT_ID = NULL
IF @WEIGHT_ID = -1     SET @WEIGHT_ID = NULL
IF @RELIGION_ID = -1     SET @RELIGION_ID = NULL
IF @GENDER_ID = -1   SET @GENDER_ID = NULL
IF @CASTE_ID = -1   SET @CASTE_ID = NULL
IF @MARITIAL_STATUS_ID = -1 SET @MARITIAL_STATUS_ID = NULL
IF @EDUCATION_ID = -1 SET @EDUCATION_ID = NULL
IF @STATE_ID = -1 SET @STATE_ID = NULL
IF @COUNTRY_ID = -1 SET @COUNTRY_ID = NULL
IF @PROFESSION_ID = -1 SET @PROFESSION_ID = NULL
IF @CITIZENSHIP_ID = -1 SET @CITIZENSHIP_ID = NULL
IF @IS_PHOTO_ID = -1 SET @IS_PHOTO_ID = NULL
IF @SEEKING_ID = -1 SET @SEEKING_ID = NULL
IF @SEARCH_QUERY = '' SET @SEARCH_QUERY = NULL

SELECT  * FROM DRM_PERSONALS_M_VIEW
WHERE  
(@HEIGHT_ID IS NULL OR @HEIGHT_ID = HEIGHT_ID) AND
(@WEIGHT_ID IS NULL OR @WEIGHT_ID =  WEIGHT_ID) AND
(@RELIGION_ID IS NULL OR @RELIGION_ID =  RELIGION_ID) AND
(@GENDER_ID IS NULL OR @GENDER_ID =  GENDER_ID) AND
(@CASTE_ID IS NULL OR @CASTE_ID =  CASTE_ID) AND
(@MARITIAL_STATUS_ID IS NULL OR @MARITIAL_STATUS_ID =  MARITIAL_STATUS_ID) AND
(@EDUCATION_ID IS NULL OR @EDUCATION_ID =   EDUCATION_ID) AND
(@STATE_ID IS NULL OR @STATE_ID =   STATE_ID) AND
(@COUNTRY_ID IS NULL OR @COUNTRY_ID =   COUNTRY_ID) AND
(@PROFESSION_ID IS NULL OR @PROFESSION_ID =   PROFESSION_ID) AND
(@CITIZENSHIP_ID IS NULL OR @CITIZENSHIP_ID =   CITIZENSHIP_ID) AND
(@IS_PHOTO_ID IS NULL OR @IS_PHOTO_ID =   IS_PHOTO_ID) AND
(@SEEKING_ID IS NULL OR @SEEKING_ID =    SEEKING_ID)

ORDER BY PROFILE_ID



Start Free Trial