Dynamic search
- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usd_rs_GetPropertySearchDetails]
@TransactionType varchar(50) ,
@PropertyType varchar(50) ,
@City varchar(50) ,
@Locality varchar(50) ,
@Area varchar(50) ,
@TotalPriceinRupeesL numeric(18,2) ,
@TotalPriceinRupeesU numeric(18,2) ,
@BedRooms varchar(50),
@SortedColumn varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
DECLARE @SQL VARCHAR(5000)
-- Insert statements for procedure here
SET @SQL='SELECT '+'''~/Images/PropertyImages/'''+ '+ PropertyImage as PropertyImage, projectname,
Lid,PropertyDescrription,City,Locality,TotalPriceinRupees,PostedDate from ListProperty
where TransactionType='''+@TransactionType+''' and PropertyType='''+@PropertyType+''' and
City='''+@City+''' And Locality = '''+@Locality+''' And CAST(Area AS Integer) >='+ @Area
+ ' And TotalPriceinRupees between '+ CAST(@TotalPriceinRupeesL as varchar) + ' and ' + CAST(@TotalPriceinRupeesU AS Varchar)
IF ISNULL(@BedRooms,'')<>''
SET @SQL=@SQL+' And CAST(BedRooms AS Integer) >='+ @BedRooms
if @SortedColumn='2'
BEGIN
SET @SQL=@SQL+' order by Locality asc'
END
if @SortedColumn='3'
BEGIN
SET @SQL=@SQL+' Order By PostedDate desc'
END
PRINT(@SQL)
EXEC(@SQL)
END
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usd_rs_GetPropertySearchDetails]
@TransactionType varchar(50) ,
@PropertyType varchar(50) ,
@City varchar(50) ,
@Locality varchar(50) ,
@Area varchar(50) ,
@TotalPriceinRupeesL numeric(18,2) ,
@TotalPriceinRupeesU numeric(18,2) ,
@BedRooms varchar(50),
@SortedColumn varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
DECLARE @SQL VARCHAR(5000)
-- Insert statements for procedure here
SET @SQL='SELECT '+'''~/Images/PropertyImages/'''+ '+ PropertyImage as PropertyImage, projectname,
Lid,PropertyDescrription,City,Locality,TotalPriceinRupees,PostedDate from ListProperty
where TransactionType='''+@TransactionType+''' and PropertyType='''+@PropertyType+''' and
City='''+@City+''' And Locality = '''+@Locality+''' And CAST(Area AS Integer) >='+ @Area
+ ' And TotalPriceinRupees between '+ CAST(@TotalPriceinRupeesL as varchar) + ' and ' + CAST(@TotalPriceinRupeesU AS Varchar)
IF ISNULL(@BedRooms,'')<>''
SET @SQL=@SQL+' And CAST(BedRooms AS Integer) >='+ @BedRooms
if @SortedColumn='2'
BEGIN
SET @SQL=@SQL+' order by Locality asc'
END
if @SortedColumn='3'
BEGIN
SET @SQL=@SQL+' Order By PostedDate desc'
END
PRINT(@SQL)
EXEC(@SQL)
END
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home