Try:

SELECT Q.Qname

FROM Question q

INNER JOIN Category_Details cd ON cd.idquestion = q.idquestion

INNER JOIN Category c ON c.idCategory = cd.idCategory

WHERE CategoryName = @CategoryName

... you can also eliminate the RETURN SCOPE_IDENTITY().  That only makes sense when you're inserting into a table with an IDENTITY column, and in this case no inserts are happening.