CODEWITHSUNDEEP
sql
Early Burly
Early Burly

Reputation: 11

Check if column value exists before SELECT

I have the following SQL statement:

SqlCommand myCmd = new SqlCommand("select distinct(year(date_created)) as theYear from content where folder_id = 1 or folder_id = 2 order by theYear desc", myConn);

This statement checks folders for any content then sets a drop down list to equal the year. However, if there is no content in either of those folders the statement bombs out. This apparently happens each new year when content hasn't been created yet. The simple answer is to merely create a piece of content, which fixes the problem. I want to make sure it's set up so that nobody has to worry about it in case something goes wrong. So, is there a way to check if there is a value in that column before it tries to select.

I tried the following:

SELECT DISTINCT (ISNULL(year(date_created), year(getdate()))) AS theYear
FROM content
WHERE folder_id = 1
    OR folder_id = 2
ORDER BY theYear DESC

that didn't seem to work. I also tried the COUNT, but either my syntax was wrong or I was using it incorrectly.

Can anybody let me know where I went wrong and what the proper method would be?

Upvotes: 1

Views: 101

Answers (2)

Andrew O'Brien
Andrew O'Brien

Reputation: 1823

Your updated statement will still apply the where clause preventing the return of any records, NULL or otherwise.

select distinct
    (ISNULL(year(date_created), year(getdate()))) as theYear
from content
where folder_id = 1
    or folder_id = 2
order by theYear desc

Try changing it to

DECLARE @yearTbl TABLE 
(
    date_created DATE
)

INSERT @yearTbl
SELECT date_created
FROM content
WHERE folder_id = 1
    OR folder_id = 2

IF (SELECT count(*) FROM @yearTbl) = 0
    SELECT year(getdate()) AS theYear
ELSE
    SELECT DISTINCT year(date_created) AS theYear
    FROM @yearTbl
    ORDER BY theYear

Upvotes: 0

GarethD
GarethD

Reputation: 69809

You can just use UNION to tack the current year on to the dataset. Since UNION removes duplicates it doesn't matter if it already exists in Content:

SELECT  YEAR(date_created) AS theYear
FROM    Content
WHERE   folder_id IN (1, 2)
UNION
SELECT  YEAR(GETDATE())
ORDER BY theYear DESC;

If you only want the current year to appear if there is no data, then you can put and EXISTS check in the second query:

SELECT  YEAR(date_created) AS theYear
FROM    Content
WHERE   folder_id IN (1, 2)
UNION
SELECT  YEAR(GETDATE())
WHERE   NOT EXISTS (SELECT 1 FROM Content WHERE folder_id IN (1, 2) )
ORDER BY theYear DESC;

Upvotes: 1

Related Questions