Don't know but i found some columns were different anyways run this query in SQL Query analyser and if it works use in SP
declare @House_P table(Id smallint, Behcode varchar(10),Name varchar(50), date datetime,Model varchar(50),H_name varchar(50),color varchar(10),price int,image varchar(20),image1 varchar(20),image2 varchar(20),description varchar(max),country varchar(20))
insert into @House_p
select 1, '1111', 'Iron', '2012-03-30 13:34:14.000','x152x','Electric','yellow','200000','3.jpg','4.jpg','5.jpg','this is Iron','italy'
union all
select 2, '1111', 'Vacuum Cleaner', '2012-04-01 13:34:14.000','y12e','Electric','red','50000','6.jpg','7.jpg','8.jpg','this is Vaccum cleaner','germany'
union all
select 3, '2222', 'Scarf', '2012-04-02 13:34:14.000','spring','Cloth','blue','10000','10.jpg','11.jpg','12.jpg','this is scarf','france'
union all
select 2, '2222', 'shirt', ' 2012-04-03 13:34:14.000','winter','Cloth','white','120000','15.jpg','13.jpg','14.jpg','this is shirt','italy'
declare @House_info table(Id smallint, Behcode varchar(10), Name varchar(30), address varchar(30),description varchar(Max),H_name nvarchar(20),Image_behcode nvarchar(30),image_header nvarchar(30), tell int,service1 nvarchar(20), service2 nvarchar(20), service3 nvarchar(20), service4 nvarchar(20), service5 nvarchar(20))
insert into @House_info
select 1, '1111','sara','paris',' ','Electric','1.jpg','2.jpg','88875646','Helpful','Garranty','Service',' ',' '
union all
select 2, '2222', 'jack', 'italy',' ','Cloth','3.jpg','4.jpg','77779556','Garranty','service1','Modify',' ',' '
union all
select 3, '3333', 'Jennifer', 'france',' ','Furniture','6.jpg','7.jpg','4587896','help1','Edit','Service3',' ',' '
DECLARE @H_name VARCHAR(50)
SET @H_name = 'Electric'
select distinct House_Info.Image_Behcode,House_Info.Image_Header,House_Info.Name,substring(House_Info.[Service1]+'-'+House_Info.[Service2]+'-'+House_Info.[Service3]+'-'+House_Info.[Service4]+'-'+House_Info.[Service5]+'-'+House_Info.[Service5],0,46)+'...'as Description,substring(House_Info.[Description],0,45)+'...' As Description2 ,SUBSTRING(House_Info.[Address],0,26)+'...' as Address,MAX(House_p.[Date]) as [Maxdate]
into #Temp
from @House_Info House_Info
Inner Join @House_p House_p ON House_Info.Behcode = House_p.Behcode
where House_Info.H_name=@H_name
group by House_Info.address,House_Info.Name,House_Info.Image_Behcode,House_Info.Image_Header,House_Info.Service1,House_Info.Service2,House_Info.Service3,House_Info.Service4,House_Info.Service5,House_Info.Description
order by [Maxdate] desc
SELECT ROW_NUMBER() OVER
(
order by [Maxdate] desc
)AS RowNumber, *
INTO #Results
FROM #Temp
--SELECT @RecordCount = COUNT(*)
--FROM #Results
SELECT * FROM #Results
--WHERE (RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1)
DROP TABLE #Results
DROP TABLE #Temp
