CASE ELSE and order? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE ELSE and order?

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-23 : 16:01:11

I managed to get this working for a similar query but this one is slightly different in that we have one more parameter added which is the sortdir.

quote:

WITH SDSProductSummary AS
(
SELECT p.[SDSID], p.Product, p.SecondName, p.Manufacturer, p.Category,
ROW_NUMBER() OVER (ORDER BY
CASE @sortDir
WHEN 'DESC' THEN NULL
ELSE
CASE @sortByFieldName
WHEN 'SDSID' THEN CAST(SDSID as varchar(50))
WHEN 'Product' THEN Product
WHEN 'CommonName' THEN SECONDNAME
WHEN 'Manufacturer' THEN MANUFACTURER
WHEN 'Category' THEN CATEGORY
END
END ASC) AS RowNumberAsc,
ROW_NUMBER() OVER (ORDER BY
CASE @sortDir
WHEN 'ASC' THEN NULL
ELSE
CASE @sortByFieldName
WHEN 'SDSID' THEN CAST(SDSID as varchar(50))
WHEN 'Product' THEN Product
WHEN 'CommonName' THEN SECONDNAME
WHEN 'Manufacturer' THEN MANUFACTURER
WHEN 'Category' THEN CATEGORY
END
END DESC) AS RowNumberDesc
FROM v_SDS_Summary p
)

SELECT
CASE @sortDir
WHEN 'DESC' THEN RowNumberDesc ELSE RowNumberAsc
END AS 'RowNumber', [SDSID], Product, SecondName, Manufacturer, Category
FROM SDSProductSummary
WHERE CASE @sortDir
WHEN 'DESC' THEN RowNumberDesc ELSE RowNumberAsc
END
BETWEEN @firstRow AND @lastRow
ORDER BY CASE @sortDir WHEN 'DESC' THEN RowNumberDesc ELSE RowNumberAsc END

Basically I want to be able to put the int fields into its own CASE statement (CASE @sortByFieldName) and strings to have the same - another CASE @sortByFieldName for strings.

however, this does not work in terms of the syntax I am trying to construct.

where am I going wrong?

so this works but without the @sortDir case statement:

quote:

WITH TracksSummary AS
(
SELECT
p.TrackID, p.ArtistID, p.GenreID, p.TrackName, p.FullDuration, p.Tempo,
ROW_NUMBER() OVER
(
ORDER BY
CASE @sortByFieldName
WHEN 'ID' THEN TrackID -- CAST(TrackID as nvarchar(50))
WHEN 'Duration' THEN FullDuration --CAST(FullDuration as nvarchar(50))
WHEN 'aid' THEN ArtistID --CAST(ArtistID as nvarchar(50))
END ASC,
CASE @sortByFieldName
WHEN 'TrackName' THEN TrackName
WHEN 'Tempo' THEN Tempo
END ASC
) AS RowNumber
FROM Tracks p
WHERE (@columnValue IS NULL AND @columnName IS NULL)
OR
CASE @columnName
WHEN 'TrackId' THEN CAST(p.TrackID as nvarchar(100))
WHEN 'TrackName' THEN p.TrackName
WHEN 'FullDuration' THEN CAST(p.FullDuration as nvarchar(100))
WHEN 'ID' THEN CAST(p.TrackID as nvarchar(100))
WHEN 'ArtistId' THEN CAST(p.ArtistID as nvarchar(50))
END
LIKE @columnValue

)
SELECT
RowNumber, TrackID, ArtistID, GenreID, TrackName, FullDuration, Tempo
FROM
TracksSummary
WHERE
RowNumber BETWEEN @firstRow AND @lastRow
ORDER BY
CASE @sortByFieldName
WHEN 'ID' THEN TrackID -- CAST(TrackID as nvarchar(50))
WHEN 'Duration' THEN FullDuration --CAST(FullDuration as nvarchar(50))
WHEN 'aid' THEN ArtistID --CAST(ArtistID as nvarchar(50))
END ASC,
CASE @sortByFieldName
WHEN 'TrackName' THEN TrackName
WHEN 'Tempo' THEN Tempo
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-23 : 16:32:43

Are you getting syntax errors? If so, please post them here. Also, after the CTE, can you just write SELECT * FROM SDSProductSummary and have it run successfully?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources