Subquery results in SP getting lost when called in .Net App

Dominic Lennon
Dominic Lennon used Ask the Experts™

I've written a Stored Procedure below:-

CREATE PROCEDURE [dbo].[getQuestionsByTopic]
      -- Add the parameters for the stored procedure here
      @topicId int,
      @quoteId int
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

    -- Insert statements for procedure here
      SELECT q.*, a.questionAnswer, a.answerId FROM question q
      LEFT JOIN (SELECT questionId, questionAnswer, quoteId, Id AS answerId FROM questionAnswer WHERE quoteId = @quoteId) AS a ON q.questionId = a.questionId
      WHERE q.topicId = @topicId;

When ran from SQL Management Studio, it returns what I want - all questions and any answers based on Quote ID. However, when I call it in my .Net app, the fields specifically in the subquery never appear. Any ideas? Here's my code which grabs a dataset anyway:-

public DataTable getQuestionsByTopic (int topicId, int quoteId) {
                  DataTable dt = new DataTable();
                  SqlConnection conn = new SqlConnection();
                        using (SqlCommand command = new SqlCommand()) {
                              command.Connection = conn;
                              command.CommandType = CommandType.StoredProcedure;
                              command.CommandText = "getQuestionsByTopic";
                    command.Parameters.AddWithValue("@quoteId", topicId);
                    command.Parameters.AddWithValue("@topicId", topicId);

                              try { using(SqlDataAdapter adapt = new SqlDataAdapter(command)) adapt.Fill(dt); }
                              catch (Exception ex) { WritetoEventLog(ex.Message); }
                              finally { conn.Close(); }
                  return dt;
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

I suggest you replace SELECT q.* with the actual column list and to be very specific you could even cast each to the data type expected by the .NET page code.
be sure with the parameters you are passing, i see in your code you are passing topicid in both the parameters


Jesus christ. How obvious was that! Thanks for the answer - no wonder I never got the results I was after.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial