columnname not passed

Posted by Dn2010 under ASP.NET on 4/24/2012 | Points: 10 | Views : 896 | Status : [Member] | Replies : 2
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.




Responses

Posted by: Bugwee on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
this returns nothing because the parameter value for the @columnname is empty
declare @ee int;

exec jobsearchList 1,3,31,'','asp',@ee output


change to
declare @ee int;

exec jobsearchList 1,3,31,'jobs_desc','asp',@ee output


Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
thanks,

but i already tried it,

declare @ee int;

exec jobsearchList 1,3,31,'jobs_desc','asp',@ee output

its not working,

please give any other solution,.

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response