am doing a search and am passing
-----------------------------
Exec pr_GetJobs '','China,Iran',1,20,'1,2',7
==============================
CREATE proc pr_GetJobs
(
@JobLocations varchar(100),
@Locations varchar(200),
@cde_MinExperience int,
@cde_maxExperience int,
@JobCategory varchar(100),
-- @Role varchar(100),
@Jobfreshness int
)
as
Begin
set nocount on
declare @sql varchar(8000)
select @sql = 'select distinct j.cde_id as Jobid,j.txt_JobTite,j.txt_JobDescription as Description,
convert(varchar(2),j.cde_MinExperience) + '' To '' + convert(varchar(2),j.cde_maxExperience) + '' Year(s)'' as txt_Experience,
j.txt_Location,j.cde_Vacancy,j.txt_requirements,j.cde_RecruitrId,isnull(r.txt_companyName,''AdminCompany'') as txt_companyname,
convert(varchar(15),dt_PostDate,107) as PostedDate,convert(varchar(15),dt_EndDate,107) as dt_EndDate from t_Job j left join
t_RecruitersProfile r on r.cde_LoginID=j.cde_RecruitrId left join t_job_lnl_Location_JobPosting jl on jl.cde_JobId=j.cde_id
left join t_Job_lnk_Category_jobposting jc on jc.cde_jobId=j.cde_id where j.dt_EndDate>=getdate() and flg_Active=1'
if(@JobLocations != '')
select @sql = @sql + ' and jl.cde_LocId in(' + convert(varchar(100),@JobLocations) + ')'
if(@Locations != '')
select @sql = @sql + ' and j.txt_Location in(' + convert(varchar(100),@Locations) + ')'
if((@cde_MinExperience != 0) and (@cde_maxExperience != 0))
select @sql = @sql + ' and j.cde_MinExperience>=' + convert(varchar(100),@cde_MinExperience) + 'and j.cde_maxExperience<=' + convert(varchar(100),@cde_maxExperience) + ''
if(@JobCategory != '')
select @sql = @sql + ' and jc.cde_catId in(' + convert(varchar(100),@JobCategory) + ')'
if(@Jobfreshness != 0)
select @sql = @sql + ' and j.dt_PostDate>=(getdate()-' + convert(varchar(100),@Jobfreshness) + ')'
select @sql
Exec (@sql)
set nocount off
End
====================
Exec pr_GetJobs '','China,Iran',1,20,'1,2',7
====================
returns error
am passing the value('China,Iran') from a textbox it is allowed to enter nmultiple values seperated by commas
in the above
how can i solve
|
| Author: mythili d 04 Sep 2008 | Member Level: Silver | Rating:  Points: 3 |
I' m not sure if I understood it correctly the variable @joblocations is getting the value China and the @Location is getting iran if yes isnt this suppose to be in a string in the sql meaning
select * from tblJobs where joblocation='china' for example in that case the tsql that you are using needs '''+@joblocations+'''' and '''+@locations +''' for all that is suppose to go in as string should be in 3 single quotes alternatively I could suggest to print the content of the @sql variable and execute that sql statement that way it would be easier for you to correct these kind of errors.
example
declare @sql nvarchar(1000)
declare @desc varchar(100)
set @desc=('ST-CRCL INTRCHBL HP EAR-GEN ')
set @sql=('select *from tblItem where desc=''' +@desc+'''')
print @sql
exec @sql
Hope that helps
thanks
|