October 24, 2015 at 2:20 am
Hi all, I am trying to create a stored proc for a web app that lets users search vacation destinations that meet their requirements. In the web app there are search criteria checkboxes such as Price, Consumer Ratings, etc that conumers can check. Next to each criteria is a corresponding dropdown containing a weight number.
After checking their desired search criterias and giving each a weight number, the user clicks the Submit button and sends the data to a storedproc. The storedproc will query a database table that holds info related to hotels and vacation destinations. The table contains column names that match the names of the search criterias.
Basically the stored proc will return a search result and order them according to the weight given to each search criteria.
The following is what I would like to achieve
create proc spVacationSearch
@Price decimal(12,2),
@customerRating int,
@priceWeight int,
@ratingWeight int
As
Begin
Select Top(10) HotelName, Price, CustomerRating From dbo.VacationDestinations
where Price <= @Price AND CustomerRating = @CustomerRating
Order By Price ASC, CustomerRating DESC
End
The columns in the Order By clause should be ordered by their corresponding weight (The one with the greatest weight number is listed first in the Order By clause followed by the column with the second highest weight and so on). Please help me create a query using either a Procedural or a Set-Based approach. Thanks in advance.
October 24, 2015 at 5:22 am
October 24, 2015 at 7:21 am
Hi,
Thanks for replying. Suppose the user provides the following data in the web app and clicks the Submit button
@Price = 1350.00
@customerRating = 5
@priceWeight = 1
@ratingWeight = 5
Since the value of @ratingWeight is greater than @priceWeight, CustomerRating will be listed before Price
in the Order By clause as shown below
Select Top(10) HotelName, Price, CustomerRating
From dbo.VacationDestinations
where Price <= @Price AND CustomerRating = @CustomerRating
Order By CustomerRating DESC, Price ASC
I have tried to use dynamic SQL to solve this problem but I got stuck. Also if there are more search criterias than what I have shown in this example,
there will be more columns in the Order By clause.
That means Case statements are not suitable for teasing out all the possible combination of column orders in the Order By clause. It would require the
use of some kind of a loop and a bunch of if statements.
October 24, 2015 at 10:56 am
October 24, 2015 at 6:09 pm
I'll never understand why anyone considers dynamic SQL to not be an elegant solution when it's so bloody effective (and safe) when done correctly.
--Jeff Moden
October 24, 2015 at 7:53 pm
Hi, the following are table schema and my sample data
dbo.VacationDestination(ID int not null identity(1,1) primary key, HotelName nvarchar(250),
Price decimal(10,2), CustomerRating int, Location nvarchar(250) )
-----------------------------------------------------------------------------------------
ID HotelName Price CustomerRating Location
-----------------------------------------------------------------------------------------
1 Wynn Las Vegas $305.00 4 Las Vegas, NV 89109
-----------------------------------------------------------------------------------------
2 Santorini Grace $345.00 5 Santorini, Greece
-----------------------------------------------------------------------------------------
3 Halekulani Hotel $319.00 4 Honolulu, HI 96815
-----------------------------------------------------------------------------------------
4 British Colonial $335.00 4 Nassau, Bahamas
-----------------------------------------------------------------------------------------
5 Edgewater Beach $325.00 4 Naples, Florida
-----------------------------------------------------------------------------------------
6 Iberostar Cancún $340.00 5 Cancún, Mexico
-----------------------------------------------------------------------------------------
The requirements are users can search the table based on the following criterias
[highlight]
Price, UserRating, and Location
[/highlight]
Also the results must be listed according to the weight associated with each search criteria.
October 24, 2015 at 8:33 pm
SQLUSERMAN (10/24/2015)
Hi,Thanks for replying. Suppose the user provides the following data in the web app and clicks the Submit button
@Price = 1350.00
@CustomerRating = 5
@priceWeight = 1
@ratingWeight = 5
Since the value of @ratingWeight is greater than @priceWeight, CustomerRating will be listed before Price
in the Order By clause as shown below
Select Top(10) HotelName, Price, CustomerRating
From dbo.VacationDestinations
where Price <= @Price AND CustomerRating = @CustomerRating
Order By CustomerRating DESC, Price ASC
I have tried to use dynamic SQL to solve this problem but I got stuck. Also if there are more search criterias than what I have shown in this example,
there will be more columns in the Order By clause.
That means Case statements are not suitable for teasing out all the possible combination of column orders in the Order By clause. It would require the
use of some kind of a loop and a bunch of if statements.
So, what do you want to do in the case of the tie you have between CustomerRating and RatingWeight?
--Jeff Moden
October 24, 2015 at 9:47 pm
Hi, thanks for replying. I made sure there will be no ties using javascript on the frontend.
October 25, 2015 at 3:16 am
declare @price float = 200
declare @pricew int = 1
declare @customerrating int =3
declare @ratingweight int =5
select * from [dbo].[VacationDestination]
where price >=@price and userrating >@customerrating
order by ntile(@customerrating) over(order by userrating) desc,
ntile(@pricew) over(order by @price) desc
First crack at it , change the weight and let me know if the order of the rows are as what your expecting
October 25, 2015 at 7:10 am
Jeff Moden (10/24/2015)
I'll never understand why anyone considers dynamic SQL to not be an elegant solution when it's so bloody effective (and safe) when done correctly.
I once fixed a stored procedure written correctly and with proper usage of sp_executesql which contained 2000 lines of code.
It was fixed with performance improvement in the range of 100X with a simple CLR Assembly , (lines of code in assembly 4). I have created a video for it too.
There was another code which created tables for ETL processes using very complicated but technically correct dynamic sql when its easily achieved using a script task in SSIS.
Dynamic SQL is elegant and provides flexibility just not in all cases.
Think of it this way just because we can manage SQL using DDL withint SSMS doesnt mean its better than power shell.
Sometimes it is sometimes it isnt 🙂
October 25, 2015 at 8:58 am
Jayanth_Kurup (10/25/2015)
Jeff Moden (10/24/2015)
I'll never understand why anyone considers dynamic SQL to not be an elegant solution when it's so bloody effective (and safe) when done correctly.I once fixed a stored procedure written correctly and with proper usage of sp_executesql which contained 2000 lines of code.
It was fixed with performance improvement in the range of 100X with a simple CLR Assembly , (lines of code in assembly 4). I have created a video for it too.
There was another code which created tables for ETL processes using very complicated but technically correct dynamic sql when its easily achieved using a script task in SSIS.
Dynamic SQL is elegant and provides flexibility just not in all cases.
Think of it this way just because we can manage SQL using DDL withint SSMS doesnt mean its better than power shell.
Sometimes it is sometimes it isnt 🙂
Heh... if it had 2000 lines of code in it, it probably wasn't written correctly and virtually any change would have been an improvement. 😛 You have piqued my interest, though (especially since you converted 2000 lines of code to just 4). What's the URL for that video?
I agree that there are times that the use of SQLCLR is wonderful thing (delimited splitters are a great example) but I've also seen it where SQLCLR is the worst possible thing that can be done (making a call to RegEx when a even a complex LIKE would do and making "one for all" audit triggers are fairly recent examples that I've had to repair. Got 600X improvement by replacing that!). As with anything else, "It Depends".
The same holds true for the use of PowerShell. For example, I think it's wonderful that someone can build a PoSh system that will take care of all backups in an entire enterprise... until the server that system is on fails and all the log files on many of the other servers consume all of the hard disk space because they're no longer being backed up (as just one example).
Both SQLCLR and PoSh (as is SSIS, SSRS, SSAS, PowerPivot, and a whole bunch of other things) are powerful tools when used appropriately. The trouble is that a lot of people tend to use them as a crutch to do things that are actually pretty easy to do in SQL correctly and quickly but they just don't know how to do. I even had one guy tell me that it was critical that we get his SQLCLR function into SQL Server right away because it was critical to make his SQL work. It was an SQLCLR to do a bloody modulus, which he didn't know how to do in SQL.
Which leads me to this statement that you made...
Think of it this way just because we can manage SQL using DDL withint SSMS doesnt mean its better than power shell.
While I absolutely agree with that (and I'll include SQLCLR in that), my observation has been that it's usually not true. That's why I have the play on words in my signature...
"Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."
... and ignorance of T-SQL and some of the older, less sexy tools is no excuse.
--Jeff Moden
October 25, 2015 at 9:09 am
Hi jeff
here is the link to the video
https://www.youtube.com/watch?v=5sOlIIC-UXg
The procedure was used to decides deadline for when certain business processes should complete. It required people to fetch items from a queue and then show them the deadline in their local time. Tasks had different priorities which resulted in different deadlines, users are in different timezone which required knowledge of their timezone keeping in mind DST , countries where multiple timezone are in effect, the fact that countries didn't have fixed dates for DST also didnt help , holidays in the country and weekends needs to be accounted for. On top of that it was possible to hand over items to people in other timezone at which point a whole other set of validations were required to ensure the handover was possible only if the deadline was reasonable while still keeping track of original times of assigment and completion for audit purposes.
Also did I mention the server Failed over from East coast to west coast then we needed to acount for the change in Getdate() in calculating these deadlines 🙂
EDIT I seriously considered add when replying to the post before this 🙂
Just because you CAN do something in T-SQL, doesn't mean you MUST.
October 25, 2015 at 9:39 am
Jayanth_Kurup (10/25/2015)
Just because you CAN do something in T-SQL, doesn't mean you MUST.
Thanks for the link to the video and your explanation.
As to your comment above, my point is that a lot of people don't even know that doing something in T-SQL is even possible never mind if it's the better solution or not. This is particularly true when it comes to things like SSIS and ETL in general. I've repaired several systems that were truly a "Tower of Babel" in that they used Active-X, Perl, custom written EXEs, SQLCLR, PowerShell, Python, etc, etc and no one person could make changes to it not to mention all the externally stored code that needed to be recompiled.
The worst part of it all is that is was all grossly ineffective because people didn't understand even their own pet methods when it came to how to do things in a database. One such process took 40 minutes just to get one 30MB file ready for import (admittedly, they were truly odd and complicated files that no one should have to tolerate but DoubleClick.net made their own rules) never mind doing the actual import and we were importing more than a hundred such files per day. If you do the math there, we never caught up. After rewriting the code using only T-SQL, we were importing and doing final processing on 8 files every minute.
Again, my point is that there are a whole lot of people out there that use their favorite tool because they simply don't know what they're doing in databases. That idiot that wrote an SQLCLR to do a modulus is a classic example.
--Jeff Moden
October 25, 2015 at 10:16 am
I agree, people use what little they know to create convoluted solutions to simple everyday problem.
I just imagine that somewhere there is a dot net guy on a forum asking why the database folk jump thru hoops trying to write everything in SQL when it could easily be done in their chosen technology 😉
October 25, 2015 at 10:31 am
Jayanth_Kurup (10/25/2015)
I agree, people use what little they know to create convoluted solutions to simple everyday problem.I just imagine that somewhere there is a dot net guy on a forum asking why the database folk jump thru hoops trying to write everything in SQL when it could easily be done in their chosen technology 😉
We database "folk" don't try to write everything in SQL. For example, I'll be the first to say that using SQLCLR to do delimited string splits is the best way to go. Looking at your time zone example assembly, I'd say that it actually could be done in T-SQL fairly easily (certainly NOT requiring 2000 lines of code) but I'll also say that, except for needing to set TRUSTWORTHY on a database and a couple of other concerns, your assembly is a great example of when an assembly provides good value in its simplicity.
I'll also suggest that this is a type of formatting function and should be relegated to the application and not SQL Server.
As a bit of a sidebar, Paul White's SQLCLR to do delimited splits doesn't require TRUSTWORTHY nor does it require UNSAFE (less of a concern in this case, for sure)
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply