SQL return value as a parameter

Question

I had asked a question yesterday here question

Now I changed my codes as below

declare @like bit, @dislike bit

IF EXISTS (select * from likeordislike 
           where comment_id = @comment_id and likevar = 1 and dislikevar = 0)
BEGIN
    SET @like    = 'true'
    SET @dislike = 'false'
END
ELSE IF EXISTS (select * from likeordislike 
                where comment_id = @comment_id and likevar = 0 and dislikevar = 1)
BEGIN
    SET @like    = 'false'
    SET @dislike = 'true'
END
ELSE
BEGIN
    SET @like = 'false'
    SET @dislike ='false'
END

SELECT     
    comment_id, comment_post_id, comment_user_id, 
    comment_text, comment_like, comment_dislike, 
    comment_reply_to_id, comment_date,
    @like as begendi,
    @dislike as begenmedi
FROM         
    comment 
ORDER BY 
    comment_date DESC

I am trying to select comments and comments like or dislike status.

I am using a datalist on my asp.net web page.I need return value per comment. I must give @comment_id parameter,this query returns same result for every comment (begendi = 1, begenmedi = 0). How can I solve this problem?

ScopeIdentity or return value needed ? Thanks (I have two table 1-comment table, 2-likeordislike table that has comment_id, user_id, likevar, dislikevar and eventdate columns)

select * 
from likeordislike 
where comment_id = @comment_id and likevar = 0 and dislikevar = 1

Answer 1

I am not sure exactly what you are trying to achieve here, but I think you can achieve the outcome you are after without using a procedural approach and by simply using a join:

SELECT  comment_id, 
        comment_post_id, 
        comment_user_id, 
        comment_text, 
        comment_like, 
        comment_dislike, 
        comment_reply_to_id, 
        comment_date,
        begendi = CAST(CASE WHEN LikeOrDislike.Likes > 0 OR LikeOrDislike.Dislikes = 0 THEN 1 ELSE 0 END AS BIT),
        begenmedi = CAST(CASE WHEN LikeOrDislike.Dislikes > 0 AND LikeOrDislike.Likes = 0 THEN 1 ELSE 0 AS BIT)
FROM    Comment
        LEFT JOIN 
        (   SELECT  Comment_ID,
                    Likes = COUNT(CASE WHEN LikeVar = 1 AND DislikeVar = 0 THEN 1 END),
                    Dislikes = COUNT(CASE WHEN LikeVar = 0 AND DislikeVar = 1 THEN 1 END),
                    Other = COUNT(CASE WHEN LikeVar = DislikeVar THEN 1 END)
            FROM    LikeOrDislike
            GROUP BY Comment_ID
        ) LikeOrDislike
            ON Comment.Comment_ID = LikeOrDislike.Comment_ID
ORDER BY comment_date DESC