for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
The approach I typically take is this:
SELECT
someColumns
FROM
someTable
WHERE
CASE WHEN @.sortValue = 1 THEN NEWID() END,
CASE WHEN @.sortValue = 2 THEN someTextColumn END,
CASE WHEN @.sortValue = 3 THEN someDateTimeColumn END,
defaultSortColumn -- just in case the @.sortValue is not 1, 2, or 3, Iknow the results will be sorted by *something*
|||My full stored procedure is listed below but i assume i have to specify the order
by clause somewhere it keeps returning a incorrect syntax near CASE error message
The sproc
CREATE PROCEDURE [dbo].[sp_call_accomSearch]
(
@.accomType As Int,
@.sgleroom As Int,
@.dbleroom As Int,
@.twinroom As Int,
@.tripleroom As Int,
@.Garage As Int,
@.Phone As Int,
@.Altitude As Int,
@.CarPark As Int,
@.Tv As Int,
@.TownCentre As Int,
@.SwimPool As Int,
@.Radio As Int,
@.NearSlopes As Int,
@.DgsAdmit As Int,
@.Safe As Int,
@.CrossCtry As Int,
@.SuitDisable As Int,
@.Balcony As Int,
@.OnTLake As Int,
@.Solarium As Int,
@.Suite As Int,
@.QutZone As Int,
@.BeautyCb As Int,
@.Minibar As Int,
@.Tennis As Int,
@.WhirlPl As Int,
@.Elevator As Int,
@.Sauna As Int,
@.PriceRgLow As Int,
@.PriceRgHigh As Int,
@.DateFromTotal As DateTime,
@.DateToTotal As DateTime,
@.selfcatering As Int,
@.halfboard As Int,
@.fullboard As Int,
@.roomphone As Int,
@.Suitdisableroom As Int,
@.CountryID As Int,
@.OrderBy As Int
)
AS
SELECT
tblaccommodation.accommodationID,
tblaccommodation.[name],
tblaccommodation.address1,
tblaccommodation.address2,
tblaccommodation.town,
tblaccommodation.postcode,
tblaccommodation.country,
tblaccommodation.email,
tblaccommodation.contact,
tblaccommodation.editorial,
(SELECT [name] FROM tblresort WHERE resortID = resortname) As ResortName,
(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As SgleRoomTotal,
(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As dbleRoomTotal,
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As twinRoomTotal,
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As tripleRoomTotal
FROM
tblaccommodation
WHERE
tblaccommodation.accomType = case
when @.accomType = 1 then @.accomType
else tblaccommodation.accomType
end AND
(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.Sgleroom AND
(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.dbleroom AND
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.twinroom AND
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.tripleroom AND
tblaccommodation.Garage = case
when @.Garage <> 0 then @.Garage
else tblaccommodation.Garage
end AND
tblaccommodation.Phone = case
when @.Phone <> 0 then @.Phone
else tblaccommodation.Phone
end AND
tblaccommodation.Altitude = case
when @.Altitude <> 0 then @.Altitude
else tblaccommodation.Altitude
end AND
tblaccommodation.Carpark = case
when @.Carpark <> 0 then @.Carpark
else tblaccommodation.Carpark
end AND
tblaccommodation.TownCentre = case
when @.TownCentre <> 0 then @.TownCentre
else tblaccommodation.TownCentre
end AND
tblaccommodation.SwimPool = case
when @.SwimPool <> 0 then @.SwimPool
else tblaccommodation.SwimPool
end AND
tblaccommodation.NearSlopes = case
when @.NearSlopes <> 0 then @.NearSlopes
else tblaccommodation.NearSlopes
end AND
tblaccommodation.DgsAdmit = case
when @.DgsAdmit <> 0 then @.DgsAdmit
else tblaccommodation.DgsAdmit
end AND
tblaccommodation.Safe = case
when @.Safe <> 0 then @.Safe
else tblaccommodation.Safe
end AND
tblaccommodation.CrossCtry = case
when @.CrossCtry <> 0 then @.CrossCtry
else tblaccommodation.CrossCtry
end AND
tblaccommodation.SuitDisable = case
when @.SuitDisable <> 0 then @.SuitDisable
else tblaccommodation.SuitDisable
end AND
tblaccommodation.OnTLake = case
when @.OnTLake <> 0 then @.OnTLake
else tblaccommodation.OnTLake
end AND
tblaccommodation.Solarium = case
when @.Solarium <> 0 then @.Solarium
else tblaccommodation.Solarium
end AND
tblaccommodation.QutZone = case
when @.QutZone <> 0 then @.QutZone
else tblaccommodation.QutZone
end AND
tblaccommodation.BeautyCb = case
when @.BeautyCb <> 0 then @.BeautyCb
else tblaccommodation.BeautyCb
end AND
tblaccommodation.Tennis = case
when @.Tennis <> 0 then @.Tennis
else tblaccommodation.Tennis
end AND
tblaccommodation.Whirlpl = case
when @.Whirlpl <> 0 then @.Whirlpl
else tblaccommodation.Whirlpl
end AND
tblaccommodation.Elevator = case
when @.Elevator <> 0 then @.Elevator
else tblaccommodation.Elevator
end AND
tblaccommodation.Sauna = case
when @.Sauna <> 0 then @.Sauna
else tblaccommodation.Sauna
end AND
tblaccommodation.PriceRgLow >= @.PriceRgLow AND
tblaccommodation.PriceRgLow <= @.PriceRgHigh AND
tblaccommodation.PriceRgHigh <= @.PriceRgHigh AND
tblaccommodation.FromDT >= @.DateFromTotal AND
tblaccommodation.ToDT <= @.DateToTotal AND
tblaccommodation.selfcatering = case
when @.selfcatering<> 0 then @.selfcatering
else tblaccommodation.selfcatering
end AND
tblaccommodation.halfboard = case
when @.halfboard<> 0 then @.halfboard
else tblaccommodation.halfboard
end AND
tblaccommodation.fullboard = case
when @.fullboard<> 0 then @.fullboard
else tblaccommodation.fullboard
end AND
tblaccommodation.Country = @.CountryID AND
tblaccommodation.displayAcc = '1'
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDT END,
GO
|||First, I made a mistake in my example code. I mistakenly used aWHERE clause instead of an ORDER BY clause. Sorry to be confusing:-(
You are missing the ORDER BY, and you had an unneeded comma at the end. Try:
tblaccommodation.displayAcc = '1'
ORDER BY
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND
|||LOL ... i am confused most of the time anyway usually why i am on here so much
I am still getting an sql error still when i try to check the syntax of the query in question any
thoughts what is going wrong here not found anything on google groups like it yet. I have
listed the error below.
Fuzzy
Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identfying a column position. Variables are only allowed when ordering
by an expression referencing a column name
|||Hmmm, it's not liking the NEWID(). I suggest this as aworkaround, but it will force NEWID() to be generated for each row inthe resultset, which *might* be a performance hit if you have a lot ofrows:
ORDER BY
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND,
NEWID()
|||
You Can also do:
SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
While either version will work for you, I think this one might be a little cleaner so if someone else needs to update it, they can see exactly how it is supposed to use the new ID (if orderBy is 1).
Just my 1.50
Nick
nick-w wrote:
You Can also do: SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
Whileeither version will work for you, I think this one might be a littlecleaner so if someone else needs to update it, they can see exactly howit is supposed to use the new ID (if orderBy is 1).
I had thought about that, but had become concerned that NEWID()would be calculated for every row in every table, not just for theresultset. That sent me on a fruitless hunt to find a referencefor the processing sequence of all of the different portions of aSELECT statement so that I could confirm or deny that concern. When I couldn't find a reference (does anyone have one?) , I decidedon the approach I recommended. But, with that being said, I muchprefer the approach you've recommended as it doesn't make assumptionsand as you said it is cleaner. :-)
No comments:
Post a Comment