Solved

Returning a Different Value if a Column Equals a Certain Value

Posted on 2007-11-26
7
165 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:CMES-IT
  • 3
  • 2
  • 2
7 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 20350462
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 ArticleOrder ASC
END
0
 

Author Comment

by:CMES-IT
ID: 20350494
angelIII -

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.
0
 
LVL 6

Assisted Solution

by:messen1975
messen1975 earned 200 total points
ID: 20350605
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
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20350627
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
0
 

Author Comment

by:CMES-IT
ID: 20350634
Ah, so simple! Why didn't I think of that! ;)

Great, thanks a bunch messen1975.
0
 

Author Comment

by:CMES-IT
ID: 20350648
Everything's working now, thanks Angel and Messen.
0
 
LVL 6

Expert Comment

by:messen1975
ID: 20350656
No problem  :)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question