SQL Stored Procedure with IF THEN ELSE? 1

[MrPink1138]

Here is, in a nutshell, what I'm trying to do.
Two tables - articles and comments

articles has:
article_id, headline, article_text

comments has:
comment_id, article_id, comment_text, headline

Now... SOME of the comments are linked to articles in the database. Others are not because they are general comments and don't relate to any article.

I need to be able to pull out comment_id, comment_text, and headline.
Here's the catch:
For the comments that are linked to articles I need to do an INNER JOIN and pull the headline from the articles table. For the comments WHERE article_id=0 I need to pull the headline from the comments table.
My thoughts are to do something like this in my stored procedure:


CREATE PROCEDURE spCommentarySearch
(
@CommentID int
)
AS
DECLARE @ArticleID int
SELECT @ArticleID = comments.article_id FROM comments WHERE comments_id=@CommentID


IF (@ArticleID) <> 0
RETURN SELECT comment_id, comment_text,,articles.headline FROM comments INNER JOIN Articles ON Articles.ID = comments.article_id
ELSE
RETURN SELECT comment_id, comment_text, headline FROM comments
GO



This however does not appear to work. Does anyone have any thoughts on this?
Thanks in advance.

 

[Glasgow]

IF (@ArticleID) <> 0
BEGIN
RETURN SELECT comment_id, comment_text,,articles.headline FROM comments INNER JOIN Articles ON Articles.ID = comments.article_id
END
ELSE
BEGIN
RETURN SELECT comment_id, comment_text, headline FROM comments
END
GO

 

[Glasgow]

I'm just not with it today, sorry. My last post is of no value - I read it as a condition with multiple lines but it is not.

 

[donutman]

Place the RETURN statement after the SELECT statement. But don't you want the dataset to have the same number of columns regardless of which path the IF takes?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[MrPink1138]

Adding the BEGIN and END statements in fixed the SQL errors I was getting with the IF THEN

I removed the RETURN keyword altogether and everything appears to be fine now. I also then added it after the SELECT statement and it worked as well. Is there any benefit to having it versus not having it? It appeared to work fine without.

Thanks for your help!!

 

[vongrunt]

Somehow I'm confused about logic of this sproc... it checks one comment for ArticleID<>0 then returns all comments/headlines. Is there any WHERE clause missing?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[MrPink1138]

Vongrunt - You are correct. There is a huge logic flaw in this. I obviously have no clue what I'm doing which is why I'm posting here.
I need to do this on an individual record basis so for example, if I changed it to pull all comments from 2/14/2005 I'd need it to return the proper headlines for EACH of those records. Is that even possible?
Is there someway to loop through the first @ArticleID recordedset and genereate a 2nd recordset from that?

 

[vongrunt]

With one query:

select C.comment_id, C.article_id, isnull(A.headline, C.headline) as headline
from comments C
left outer join articles A on nullif(C.article_id, 0) = A.article_id

If articles.article_id is never 0 you can remove NULLIF() call. This should make query somewhat faster.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[MrPink1138]

You sir, are a genius. This is perfect and WAY simpler than what I was trying. Thank you very much for the help.