hi,

  i am designing an application using asp.net, in which i have following sql tables.

    Question(IdQuestion,QName,QType,Answer) , idquestion is IDENTITY & PrimaryKey

   Answer_Detail(idAnswerDetail,idQuestion,Options),dAnswerDetail is IDENTITY

  Category(idCategory,category),  IdCategory is IDENTITY & PrimaryKey

  Category_Detail(idCategoryDetail,idCategory,idQuestion),idCategoryDetail is IDENTITY.

i have on drop down list to select categories. if i select a particular category, all the questions related to that particular category should be shown with the help of grid view for this i had written the following stored procedure.

CREATE PROC useSelectCategory(@CategoryName Varchar(50))
As
Begin
  SELECT Qname FROM Question WHERE idquestion=(SELECT idquestion FROM Category_Details WHERE idCategory=(SELECT idCategory FROM Category WHERE CategoryName=@CategoryName))
 RETURN SCOPE_IDENTITY()
END
 

and my .net code is

 SqlConnection cn = new SqlConnection("user id=sa;password=sa123;database=questionbank;data source=MERCURY-987BECC");

SqlCommand cmd = new SqlCommand("useSelectCategory", cn);

cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.VarChar,50));
        cmd.Parameters["@CategoryName"].Value = DropDownList1.SelectedValue;

        cn.Open();
        cmd.ExecuteNonQuery();    
        cn.Close();

but i get an eror saying:  "

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression "

how can i get the questions from Question table when i select a Category.... 

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.