how to write between condition by using split function in sql server 2008

Hi,

As per my understanding for your question, a user can select multiple check-boxes randomly and you are passing a single parameter @experience. In this case, you can create and execute a dynamic sql query and get desired result.

Given below solution, I have also used a single parameter @experience in this format.

@experience = 
	minimum_experience_of_first_checkbox + "," + maximum_experience_of_first_checkbox + ";" + 
	minimum_experience_of_second_checkbox + "," + maximum_experience_of_second_checkbox + ";" + 
	..
	..
	..
	minimum_experience_of_n_th_checkbox + "," + maximum_experience_of_n_th_checkbox + ";"

Here, you can see that I have used two separators.
"," is used for minimum and maximum years and ";" is used to join checkboxes.

After this, We will just have to put a loop and prepare our dynamic sql & execute it.

DECLARE @MainSQLQuery VARCHAR(MAX)
SET @MainSQLQuery = 'select e.jobid,e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where '

DECLARE @experience VARCHAR(MAX)
SET @experience = '1,2;4,5;8,10;'

DECLARE
	@whereQuery varchar(2000),
	@OrCondition varchar(5),
	@separatorCheckBox varchar(1),
	@separatorYear varchar(1),
	@remainingCheckBoxes varchar(2000),
	@splittedCheckBoxVal varchar(500),
	@maxYear varchar(200),
	@minYear varchar(50)
	
SELECT
	@whereQuery = '',
	@OrCondition = ' OR ',
	@separatorCheckBox = ';',
	@separatorYear = ','


IF CHARINDEX(@separatorCheckBox,@experience) <= 0
BEGIN
	SET @experience = @experience + @separatorCheckBox
END


SET @remainingCheckBoxes = @experience

--Create Dynamic SQL Where Query
WHILE ( LEN(@remainingCheckBoxes) > 0 )
BEGIN
	--Split Checkboxes
	SET @splittedCheckBoxVal = SUBSTRING(@remainingCheckBoxes,1,CHARINDEX(@separatorCheckBox,@remainingCheckBoxes)-1)
	SET @remainingCheckBoxes = SUBSTRING(@remainingCheckBoxes,CHARINDEX(@separatorCheckBox,@remainingCheckBoxes)+1,LEN(@remainingCheckBoxes)-1)
	
	--Spit to get minimum and maximum year
	SET @minYear = SUBSTRING(@splittedCheckBoxVal,1,CHARINDEX(@separatorYear,@splittedCheckBoxVal)-1)
	SET @maxYear = SUBSTRING(@splittedCheckBoxVal,CHARINDEX(@separatorYear,@splittedCheckBoxVal)+1,LEN(@splittedCheckBoxVal)-1)

	--Append OR condition
	SET @whereQuery = @whereQuery + @OrCondition + '(minexperience >= ' + @minYear + ' and maxexperience <= ' + @maxYear + ')'
END

--Remove Extra OR condition appended at first position due to while loop
SET @whereQuery = SUBSTRING(@whereQuery,LEN(@OrCondition)+1,LEN(@whereQuery))

--Append this with main query
SET @MainSQLQuery = @MainSQLQuery + @whereQuery

--Finally execute this dynamic sql to get value
EXEC(@MainSQLQuery)

Hope this will help to solve your problem.

Regards,
Ambicaprasad Maurya