We help IT Professionals succeed at work.

Why DataTable is null (Stored Procedure)

brad_lub
brad_lub asked
on
I am calling the method 'IFRecentPosts(int UserID, int SOITaskID) [see code below]  which executes the SP 'IF_RecentPosts' [see SP details below].

I am able to run the query and get the appropriate results from SSMS after passing in actual values in place of the variables; however, when I execute the IFRecentPosts(int UserID, int SOITaskID) method the DataTable is empty. When I debug the method, the the variables are receiving the correct values; so the issue must be at the SP level.

Couple questions:
(1)Any thoughts as to why the results are null?
(2) Is there a way to debug the SP and see why it is not returning a result?

Thanks,

Brad

+++++++++++++++++++++++++++++++++
 public static DataTable IFRecentPosts(int UserID, int SOITaskID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(AuditData.DataContext.Connection.ConnectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter("IF_RecentPosts", conn))
                {
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@UserID", UserID);
                    da.SelectCommand.Parameters.AddWithValue("@SOITaskID", SOITaskID);

                    da.Fill(dt);
                }
            }

            return dt;
        }

Open in new window

++++++++++++++++++++++++++++++++++++++++

ALTER procedure [dbo].[IF_RecentPosts]  @UserID int, @SOITaskID int as



SELECT     post.ID, 'PP-' + postType.[Description] AS [Type], task.TaskNumber, post.[Subject], u.FirstName, u.LastName, u.TitleDesc AS Title,
                          (SELECT     COUNT(*)
                            FROM          ProgressPosts
                            WHERE      ThreadID = post.ThreadID AND ID != ThreadID) AS Replies, NULL AS Satisfied,
                          (SELECT     TOP 1 DateCreated
                            FROM          ProgressPosts
                            WHERE      ParentProgressPostID = post.ID OR
                                                   ID = post.ID
                            ORDER BY DateCreated DESC) AS [Date], task.RecommendationID, task.ID AS SOITaskID, post.ThreadID, post.TeamPost, u.ID as UserID
FROM         ProgressPosts post INNER JOIN
                      ProgressPostTypes postType ON postType.ID = post.ProgressPostTypeID INNER JOIN
                      SOITasks task ON task.ID = post.SOITaskID INNER JOIN
                      Users u ON u.ID = post.UserID
WHERE     post.ParentProgressPostID IS NULL 
			and task.ID = @SOITaskID 
			--When It is Not a team post and the user that has made the post = @UserId then return the record
			--When is is a team post then return it, in any other case do not return the record
			AND (CASE WHEN post.TeamPost = 0 AND u.ID = @UserID THEN 1
				 WHEN post.TeamPost = 1 THEN 1
				 ELSE  0 
					END) = 1
UNION
SELECT     post.ID, 'Feedback Request' AS [Type], task.TaskNumber, post.[Subject], u.FirstName, u.LastName, u.TitleDesc AS Title,
                          (SELECT     COUNT(*)
                            FROM          FeedbackRequests
                            WHERE      ThreadID = post.ThreadID AND ID != ThreadID) AS Replies, post.HasAnswer AS Satisfied,
                          (SELECT     TOP 1 DateCreated
                            FROM          FeedbackRequests
                            WHERE      ParentFeedbackRequestID = post.ID OR
                                                   ID = post.ID
                            ORDER BY DateCreated DESC) AS [Date], task.RecommendationID, task.ID AS SOITaskID, post.ThreadID, (CASE WHEN post.UserID > 0 THEN 1 ELSE 0 END), u.ID as UserID
FROM         FeedbackRequests post INNER JOIN
                      SOITasks task ON task.ID = post.SOITaskID INNER JOIN
                      Users u ON u.ID = post.UserID
WHERE     post.ParentFeedbackRequestID IS NULL and task.ID = @SOITaskID
order by [Date] DESC

Open in new window

Comment
Watch Question

Top Expert 2011

Commented:
start your procedure with

SET NOCOUNT ON

you are getting additional result sets returned for each statement specifying the number of rows
addected by the statement
Top Expert 2011

Commented:
also your union should probably be a union all
since additional work is being performed to distinct the result set
which probably isn't desired or required.

Author

Commented:
Lowfatspread -- my new SP is as follows; however, the SP is still not returning any rows.

-- Of note: I originally had this query running in a view but I wanted to sort the results of the union... and got an error message saying to effect that 'I could not order by in a view'. Could this be why I'm not getting any rows?

Brad

 

USE [audit]
GO
/****** Object:  StoredProcedure [dbo].[IF_RecentPosts]    Script Date: 06/14/2010 14:21:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[IF_RecentPosts]  @UserID int, @SOITaskID int as

SET NOCOUNT ON;

SELECT     post.ID, 'PP-' + postType.[Description] AS [Type], task.TaskNumber, post.[Subject], u.FirstName, u.LastName, u.TitleDesc AS Title,
                          (SELECT     COUNT(*)
                            FROM          ProgressPosts
                            WHERE      ThreadID = post.ThreadID AND ID != ThreadID) AS Replies, NULL AS Satisfied,
                          (SELECT     TOP 1 DateCreated
                            FROM          ProgressPosts
                            WHERE      ParentProgressPostID = post.ID OR
                                                   ID = post.ID
                            ORDER BY DateCreated DESC) AS [Date], task.RecommendationID, task.ID AS SOITaskID, post.ThreadID, post.TeamPost, u.ID as UserID
FROM         ProgressPosts post INNER JOIN
                      ProgressPostTypes postType ON postType.ID = post.ProgressPostTypeID INNER JOIN
                      SOITasks task ON task.ID = post.SOITaskID INNER JOIN
                      Users u ON u.ID = post.UserID
WHERE     post.ParentProgressPostID IS NULL
            and task.ID = @SOITaskID
            --When It is Not a team post and the user that has made the post = @UserId then return the record
            --When is is a team post then return it, in any other case do not return the record
            AND (CASE WHEN post.TeamPost = 0 AND u.ID = @UserID THEN 1
                 WHEN post.TeamPost = 1 THEN 1
                 ELSE  0
                    END) = 1
UNION ALL
SELECT     post.ID, 'Feedback Request' AS [Type], task.TaskNumber, post.[Subject], u.FirstName, u.LastName, u.TitleDesc AS Title,
                          (SELECT     COUNT(*)
                            FROM          FeedbackRequests
                            WHERE      ThreadID = post.ThreadID AND ID != ThreadID) AS Replies, post.HasAnswer AS Satisfied,
                          (SELECT     TOP 1 DateCreated
                            FROM          FeedbackRequests
                            WHERE      ParentFeedbackRequestID = post.ID OR
                                                   ID = post.ID
                            ORDER BY DateCreated DESC) AS [Date], task.RecommendationID, task.ID AS SOITaskID, post.ThreadID, (CASE WHEN post.UserID > 0 THEN 1 ELSE 0 END), u.ID as UserID
FROM         FeedbackRequests post INNER JOIN
                      SOITasks task ON task.ID = post.SOITaskID INNER JOIN
                      Users u ON u.ID = post.UserID
WHERE     post.ParentFeedbackRequestID IS NULL and task.ID = @SOITaskID
order by [Date] DESC

Open in new window

Top Expert 2011
Commented:
not sure why its failing ... have you check the server log for any errors?
does the  procedure get invoked successfully from your code (no return codes/error messages?)

to order in a view....

put a Select top 100 percent *
            from (your existing query)
            order by ....

around the query

Author

Commented:
Thanks by surrounding the query with your suggestion helped!