hi friends,
i want to search by table
this query is working(jobs_desc)
Select Row_Number() over (order by jobs_id ) as Row,jobs_id,companyId,jobs_post_date,Client,ProjectId,jobs_desc,jobs_payrate,job?s_remote_part_status From jobs where jobs.companyId =@CompanyId and jobs_desc like(@text)+'%'
i passed parameter @columnname
but its not working,
Select Row_Number() over (order by jobs_id ) as Row,jobs_id,companyId,jobs_post_date,Client,ProjectId,jobs_desc,jobs_payrate,job?s_remote_part_status From jobs where jobs.companyId =@CompanyId and @columnname like(@text)+'%'
please give the solution and please correct the procedure,
this is full procedure
alter Procedure [dbo].jobsearchList (
@PageIndex int,
@NumofRows int,
@CompanyId int,
@columnname varchar(100),
@text varchar(100),
@TotalCount int output )
As
Begin
Declare @StartIndexRow int
Set @PageIndex = @PageIndex - 1
Set @StartIndexRow = (@PageIndex * @NumofRows) + 1
Select @TotalCount = count(*) From jobs WHERE companyId = @CompanyId
If @StartIndexRow > @TotalCount
Begin
Set @PageIndex=(@TotalCount/@NumofRows)-1
Set @StartIndexRow=(@PageIndex * @NumofRows)+1
End
Create Table #tmptable(Row int,jobs_id int,companyId int,jobs_post_date datetime,Client int,ProjectId int,jobs_desc text,jobs_payrate text,jobs_remote_part_status bit)
Insert into #tmptable
Select Row_Number() over (order by jobs_id ) as Row,jobs_id,companyId,jobs_post_date,Client,ProjectId,jobs_desc,jobs_payrate,job?s_remote_part_status From jobs where jobs.companyId =@CompanyId and @columnname like(@text)+'%'
Select jobs_id,convert(varchar,jobs_post_date,1) as Date,ClientName,ProjectName,jobs_desc,jobs_payrate,Case jobs_remote_part_status When '0' Then 'Close' else 'Open' end as Status
From #tmptable
Inner Join Tblclient TC on #tmptable.Client=TC.ClientId
Inner Join Tblproject TP on #tmptable.ProjectId=TP.ProjectId
Where Row between @StartIndexRow and @StartIndexRow+@NumofRows-1 ORDER BY #tmptable.jobs_post_date desc
return @TotalCount
End
***********************************************
declare @ee int;
exec jobsearchList 1,3,31,'','asp',@ee output
***********************************************
thanks.