views:

30

answers:

2
CREATE PROCEDURE USP_SEARCH_HOTELS
(
@Text varchar(50),
@Type varchar(40)
)
AS
BEGIN

Declare @Query VARCHAR(60)


IF @Type = 'By Country'
    BEGIN
    SET @Query = 'Hotel.countryName like '+ @Text+'%'
    END
ELSE IF @Type = 'By State'
    BEGIN
    SET @Query = 'HOTEL.stateName like '+ @Text+'%'
    END
ELSE IF @Type='By Property Name'
    BEGIN
    SET @Query='hotel.propertyname like'+ @Text+'%'
    End 
ELSE IF @Type='By Rating'
     BEGIN
     SET @Query='hotel.starRating='+ Cast(@Text as INT)
     END
ELSE IF @Type='By City'
    BEGIN
    SET @Query='hotel.cityName like '+ @Text+'%'
    END

    begin
    select * from hotel,tbl_cust_info
     where
    hotel.agentID=Tbl_Cust_Info.Cust_ID
    and
    (@Query)
    end

END

WHAT IS THE ERROR IN THIS PROCEDURE PLEASE HELP.

A: 

Edit: I was a bit worried about the SQL injection possibilities in my first answer so have rewritten it.

CREATE PROCEDURE USP_SEARCH_HOTELS
(
@Text varchar(50),
@Type varchar(40)
)
AS
BEGIN

DECLARE @Query nvarchar(1000)

IF @Type = 'By Country'
    BEGIN
    SET @Query = 'Hotel.countryName like @Text + ''%'''
    END
ELSE IF @Type = 'By State'
    BEGIN
    SET @Query = 'HOTEL.stateName like @Text + ''%'''
    END
ELSE IF @Type='By Property Name'
    BEGIN
    SET @Query='hotel.propertyname like @Text + ''%'''
    End 
ELSE IF @Type='By Rating'
     BEGIN
     SET @Query='hotel.starRating='+ Cast(@Text as INT)
     END
ELSE IF @Type='By City'
    BEGIN
    SET @Query='hotel.cityName like @Text + ''%'''
    END


SET @Query = 'select * from hotel,tbl_cust_info
     where
    hotel.agentID=Tbl_Cust_Info.Cust_ID
    and ' + @Query

EXEC sp_executesql @Query, N'@Text varchar(50)', @Text=@Text

END
Martin Smith
thank you for your reply . does this procedure compile to your side.
Mohan Sharma
NOTICE IF I MAKE THIS PROCEDURE WITHOUTbegin select * from hotel,tbl_cust_info where hotel.agentID=Tbl_Cust_Info.Cust_ID and (@Query) endTHIS QUERY THIS IT GETS COMPILED
Mohan Sharma
You can't just chuck a variable containing a partial query into the middle of a query and expect it to work! You need to generate the whole dynamic SQL string and use `EXEC` or `sp_executesql`
Martin Smith
Thank you very much Martin Smith. http://www.google.com/profiles/mail2mohanpyare
Mohan Sharma
@Mohan - No problem but I hope you noticed my edit about SQL injection. This is always a concern with dynamic SQL and in your case you would need to either ensure @Text is sanitised or change the query to use the @Text parameter directly.
Martin Smith
A: 
DECLARE @Final nvarchar(1000)  -- Separate partial and final
DECLARE @Partial nvarchar(100) -- let's you maintain and debug better

SET @Final = 'select * from hotel
join tbl_cust_info 
on hotel.agentID=Tbl_Cust_Info.Cust_ID
where' + @Partial

Assumiung that you are invioking this via .NET run a Regexp on the Text to eliminate all chars that are not letters or space. Like [!@#$%^&*()?;:'"\|].

Consider rewriting as 5 sprocs (HotelsByCountry, HotelsByState, HotelsByCity, HotelsByName, HotelsByRating). That will increase you perf and let you do orderby and paging (like Row_number() OVER(order by StartDate)). It will also make them totally safe.

ZXX