SQL Stored Procedure with IF THEN ELSE?

Results 1 to 2 of 2

Thread: SQL Stored Procedure with IF THEN ELSE?

  1. #1
    Join Date
    Dec 1969

    Default SQL Stored Procedure with IF THEN ELSE?

    Here is, in a nutshell, what I&#039;m trying to do.<BR>Two tables - articles and comments<BR><BR>articles has:<BR>article_id, headline, article_text<BR><BR>comments has:<BR>comment_id, article_id, comment_text, headline<BR><BR>Now... SOME of the comments are linked to articles in the database. Others are not because they are general comments and don&#039;t relate to any article.<BR><BR>I need to be able to pull out comment_id, comment_text, and headline.<BR>Here&#039;s the catch:<BR>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.<BR>My thoughts are to do something like this in my stored procedure:<BR><BR>[code language="T-SQL"]<BR>CREATE PROCEDURE spCommentarySearch<BR>(<BR>@CommentID int<BR>)<BR>AS<BR>DECLARE @ArticleID int<BR>SELECT @ArticleID = comments.article_id FROM comments WHERE comments_id=@CommentID<BR><BR><BR>IF (@ArticleID) &#060;&#062; 0<BR> RETURN SELECT comment_id, comment_text,,articles.headline FROM comments INNER JOIN Articles ON Articles.ID = comments.article_id<BR>ELSE<BR> RETURN SELECT comment_id, comment_text, headline FROM comments<BR>GO<BR>[/code]<BR><BR><BR>This however does not appear to work. Does anyone have any thoughts on this?<BR>Thanks in advance.

  2. #2
    Join Date
    Dec 1969

    Default You can't RETURN a result set!

    You can only RETURN a single value.<BR><BR>If you intend to have the SP result in a SELECT returning records, do *NOT* use RETURN.<BR><BR>But why would you want have the SP result in recordsets with differing number of fields???<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts