CODEWITHSUNDEEP
sql-server-2008
QQping
QQping

Reputation: 1370

Select column with values greater 0

I have an item database with 8 tables. There I wanted to write a stored procedure to select all values greater 0 for a specified item ID. My problem now is the statement to return only the columns where the value of the specified item is greater 0.

Basically I write a simple select which returns the following data:

Example 1 http://i.imagebanana.com/img/rhw2sm9u/1327501679.jpg

When the Column "Cold" contains now a 0 value it returns the same structure like above but Cold is 0 instead of 120.

But if the value for Cold is 0 I don't want to get this column and discard it that I only get the remaining values:

Example 2 http://i.imagebanana.com/img/dux1gyb8/1327502046.jpg

I'm using MSSQL Management Studio 2008 and the Database is 2008 R2.

Thanks in advance and sorry for my poor English.

Upvotes: 2

Views: 3870

Answers (2)

Andriy M
Andriy M

Reputation: 77667

A query is supposed to return a fixed set of columns. There's no syntax allowing you to return some columns conditionally only.

But you can invoke a query on a condition, with the help of the IF statement. So you could first check if the row you are about to retrieve contains a non-zero value in Cold. If so, you retrieve Cold with other columns, if not, you omit it:

IF EXISTS (SELECT * FROM YourTable WHERE ItemID = @ItemID AND Cold <> 0)
  SELECT
    ItemID,
    ItemName,
    Cold
  FROM YourTable
  WHERE ItemID = @ItemID
ELSE
  SELECT
    ItemID,
    ItemName
  FROM YourTable
  WHERE ItemID = @ItemID

That is, the entire stored procedure definition would then look like this:

CREATE PROCEDURE YourStoredProcedure
  @ItemID int
AS
BEGIN
  IF EXISTS (SELECT * FROM YourTable WHERE ItemID = @ItemID AND Cold <> 0)
    SELECT
      ItemID,
      ItemName,
      Cold
    FROM YourTable
    WHERE ItemID = @ItemID
  ELSE
    SELECT
      ItemID,
      ItemName
    FROM YourTable
    WHERE ItemID = @ItemID
END

Upvotes: 3

KM.
KM.

Reputation: 103587

you have to return a "value" for each row/column in a result set. If you don't want to get 0 values, then you can turn them to NULL by using something like:

SELECT
    NULLIF(YourCol1,0) AS YourCol1, NULLIF(YourCol2,0) AS YourCol2, ....
    FROM ...

or 

SELECT
    CASE WHEN YourColumn1>0 THEN YourColumn1 ELSE 0 END AS YourColumn1,
    CASE WHEN YourColumn2>0 THEN YourColumn2 ELSE 0 END AS YourColumn2,
    ...
    FROM ...

Upvotes: 0

Related Questions