Returning a Different Value if a Column Equals a Certain Value
Hi,
I have a Stored Procedure in MS SQL Server 2005 that looks like this:
CREATE PROCEDURE spGetHeadlineNewsDetail AS
BEGIN
SELECT ArticleID, Title, ArticleOrder
FROM NewsArticle
ORDER BY ArticleOrder ASC
END
GO
What I'd like is to have this query substitute a different value (Say, a dash) for ArticleOrder if ArticleOrder = 5. In other words, instead of this result:
1 Some Title 3
2 Another Title 5
3 Third Title 2
I want this result:
1 Some Title 3
2 Another Title -
3 Third Title 2
Notice how the Order number 5 was changed to a dash, but the other Order numbers still returned correctly.
What's the tSQL code I need to use to accomplish this?
Thanks in advance.
I have a Stored Procedure in MS SQL Server 2005 that looks like this:
CREATE PROCEDURE spGetHeadlineNewsDetail AS
BEGIN
SELECT ArticleID, Title, ArticleOrder
FROM NewsArticle
ORDER BY ArticleOrder ASC
END
GO
What I'd like is to have this query substitute a different value (Say, a dash) for ArticleOrder if ArticleOrder = 5. In other words, instead of this result:
1 Some Title 3
2 Another Title 5
3 Third Title 2
I want this result:
1 Some Title 3
2 Another Title -
3 Third Title 2
Notice how the Order number 5 was changed to a dash, but the other Order numbers still returned correctly.
What's the tSQL code I need to use to accomplish this?
Thanks in advance.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE PROCEDURE spGetHeadlineNewsDetail AS
BEGIN
SELECT ArticleID, Title, case when ArticleOrder = 5 then '-' else cast (ArticleOrder as varchar(10)) end ArticleOrder
FROM NewsArticle
ORDER BY NewsArticle.ArticleOrder ASC
END
BEGIN
SELECT ArticleID, Title, case when ArticleOrder = 5 then '-' else cast (ArticleOrder as varchar(10)) end ArticleOrder
FROM NewsArticle
ORDER BY NewsArticle.ArticleOrder ASC
END
ASKER
Ah, so simple! Why didn't I think of that! ;)
Great, thanks a bunch messen1975.
Great, thanks a bunch messen1975.
ASKER
Everything's working now, thanks Angel and Messen.
No problem :)
ASKER
Thanks, that SQL works great returning the dash value when ArticleOrder is 5, but it kills the OrderBy part of the SQL clause. Is there any way to retain the OrderBy part? Right now, the dashes are appearing ahead of, say, the number 1, but those columns are actually 5, and should come after 1.