Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    7

    Unanswered: Stored procedure sort records - possible?

    Hi,

    A template ASP landing page uses the following SQL to create a list of properties, 10 at a time, for each landing page. Landing pages are dedicated to a different type of property and location.

    We wish to force certain featured properties to be on Page 1 above the remainder of the results (which are sorted by price), but need different properties to be forced depending on the page.

    At present we have a field called LANDINGFEATURE which is set as 'Yes', upon which the results are sorted (red text below), but these apply erroneously across all landing pages. So a house in New York may be forced correctly onto Page 1 of the New York landing page, but it will also be forced onto the USA property landing page.

    Therefore, is it possible to pass in an identifier to the procedure and to sort properties depending on that identifier (e.g. New York Property) above the rest of the results?

    Hope you can help. Many thanks.



    SQL PROCEDURE
    ----------------------------------

    CREATE PROCEDURE LandingPage
    @PageNumber int,
    @Country VARCHAR(100),
    @Region VARCHAR(100),
    @County VARCHAR(50),
    @Location VARCHAR(100),
    @PropType VARCHAR(50),
    @SortColumn VARCHAR(20)
    AS

    DECLARE @SQL AS NVARCHAR(max)

    BEGIN
    SET @PageNumber=(@PageNumber-1)*10
    SELECT @SQL = 'SELECT TOP(10) * FROM ('
    SELECT @SQL = @SQL + 'SELECT RowID=ROW_NUMBER() OVER (ORDER BY LandingFeature DESC,'

    IF @SortColumn = 'DESC'
    SELECT @SQL = @SQL + 'Euros DESC),'
    IF @SortColumn = 'ASC'
    SELECT @SQL = @SQL + 'Euros ASC),'
    IF @SortColumn = 'POPULAR'
    SELECT @SQL = @SQL + 'viewed DESC),'
    IF @SortColumn = 'RECENT'
    SELECT @SQL = @SQL + 'Date DESC),'

    SELECT @SQL = @SQL + 'Count(*) OVER() As TotalRecords,Date,ID,Country,Region,County,Locatio n,Type,Bedrooms,Price,Currency,Pic'
    SELECT @SQL = @SQL + ' FROM Properties WHERE DeleteMarker <> ''Yes'' AND Hide <> ''Yes'''

    IF @Country is not null
    SELECT @SQL = @SQL + ' AND Country=''' + @Country + ''''

    IF @Region is not null
    SELECT @SQL = @SQL + ' AND Region=''' + Replace(@Region,'#','''''') + ''''

    IF @County is not null
    SELECT @SQL = @SQL + ' AND County=''' + Replace(@County,'#','''''') + ''''

    IF @Location is not null
    SELECT @SQL = @SQL + ' AND Location LIKE ''%' + Replace(@Location,'#','''''') + '%'''

    IF @PropType is not null
    SELECT @SQL = @SQL + ' AND Type=''' + @PropType + ''''

    SELECT @SQL = @SQL + ') TAB '
    SELECT @SQL = @SQL + 'WHERE TAB.RowId > ' + CAST(@PageNumber AS VARCHAR)

    EXECUTE SP_EXECUTESQL @SQL
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your dynamic SQL is just begging for an SQL Injection attack.

    Use CASE statements instead, or branching logic in your sproc to run different flavors of the query depending upon that parameters supplied.

    You can sort your data like this:
    order by case when TargetField = @SuppliedParameter then 0 else 1 end asc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    7
    Hi,

    Thanks for replying.

    The code I posted is the best I have been able to find and amend and write over a couple of years of trying to find a solution. Our website was built by a developer years ago that used ASP paging which was extremely slow, being that it downloaded thousands of records only to choose 10. The SQL above gets the 10 at the server, and is tens of times faster.

    We have dozens of countries, many more regions and many many areas that can be searched on the website and that does not include searches for numbers of bedrooms, land, etc which I have taken out of the example for clarity. Writing CASE statements to include every single permutation would be an enormous nightmare, particularly as new areas are added regularly.

    I have never understood how there is not a tried and tested method for retrieving 10 records of varied data from large databases that doesn't involve dynamic SQL. I have asked on countless forums how to do this and been given the same "you can't, or dynamic SQL". I have used parameters in order to hopefully cut off some attacks but, like you say, it's not by any means perfect.

    I look at websites like Amazon or large travel sites and think "how the hell do they do it?" as they must have even more permutations of searches and they have fast, probably secure SQL.

  4. #4
    Join Date
    Nov 2008
    Posts
    7
    I'm not sure what you mean by branching logic. Can you explain?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Branching" means having several variations of your output sql in your stored procedure, and then running whichever one is appropriate based on the parameters supplied.

    Or, it is arguably better to have these as separate stored procedures, which your master sproc calls based on the parameters it receives.

    So you might have one sproc that selects by Country. One by State. Etc.
    Your main sproc would check the parameters supplied and then run the appropriate sub-procedure.

    You can also search on optimal parameters by using logic like this:

    where
    (ColumnValue1 = @Parameter1 or @Parameter1 is null)
    and (ColumnValue2 = @Parameter2 or @Parameter2 is null)
    and (etc.....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2008
    Posts
    7
    I see. Sort of.

    Not sure how it will work though, as the types of searches we need to do on our database are very varied and, if I am reading you correctly, I would need dozens of stored procedures.

    E.g.

    Users and our pages need to be able to perform searches for properties like this:

    USA + Golfing
    USA + New York
    USA + New York + Manhattan
    USA + New York + (Manhattan + Brooklyn)
    USA + New York + (Manhatten + Brooklyn) + 3 bedrooms
    USA + 3 bedrooms
    USA + Golfing + 3 bedrooms
    USA + North Carolina + Golfing + 3 bedrooms + 2 bathrooms + Under 1,000,000 USD

    or even

    All Countries + Golfing + 3 bedrooms


    How would I take into consideration all those permutations without dozens of different procedures? Maybe I'm not quite understanding what you mean?

    Thanks again for the help.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Thumbs up

    where
    (Country = @Country or @Country is null)
    and (Sport = @Sport or @Sport is null)
    and (Bedrooms = @Bedrooms or @Bedrooms is null)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2008
    Posts
    7
    I have some working code now, so thank you very much. Much appreciated.

    However, how might I search for...

    Country: USA
    Region: New York
    District: Manhattan
    OR
    District: Brooklyn
    Price: Under 1,000,000 USD

    i.e. properties that are under 1 million in either Manhattan or Brooklyn?

    Is there a way of feeding the search with an OR?

    This is the main reason why I had to use dynamic SQL to build the OR part.

    Thanks again.

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by DorsetBloke View Post
    I have some working code now, so thank you very much. Much appreciated.

    However, how might I search for...

    Country: USA
    Region: New York
    District: Manhattan
    OR
    District: Brooklyn
    Price: Under 1,000,000 USD

    i.e. properties that are under 1 million in either Manhattan or Brooklyn?

    Is there a way of feeding the search with an OR?

    This is the main reason why I had to use dynamic SQL to build the OR part.

    Thanks again.
    Yes, or usage (but is this what you really want?):
    Code:
    where (Country= USA
    and Region= New York
    and District= Manhattan)
    OR
    (Country= USA
    and Region= New York
    and District= Brooklyn)
    and Price < 1,000,000
    Last edited by corncrowe; 04-27-12 at 10:58.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dorset, there has to be SOME limit to search functionality.

    In my experience, management requirements for search features is "Search for anything, anywhere", while 99% of all user searches are MUCH more limited.

    Do some research on what the users actually do. Search functionality can become a major coding and security headache if you let it.

    A more robust option that might be worth looking into is Microsoft SQL Server's Full-Text search functionality. It is more appropriate for broad-bases searches, but more complex to implement and administer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •