CODEWITHSUNDEEP
sql.netsql-servervb.netsql-server-2012
Nick Nack
Nick Nack

Reputation: 511

How to fill a DataSet from a Stored Procedure that returns multiple resultsets?

I have a stored procedure which returns multiple resultsets with the exact same structure (Selects the same data based on criteria built via a While Loop). I need to load all of these resultsets into a single dataset as one table. What is the best way to go about this? I am using SQL Server 2012

Alter PROCEDURE proc_GetAllComUserRights
AS
DECLARE @User as nvarchar(50)
DECLARE @RowCount as int
select @Rowcount=count(*) from Com_Users;
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    -- Insert statements for procedure here

    While (@Rowcount > 0)
    BEGIN
        Select @Rowcount = @Rowcount - 1;
        Select @User = UserID FROM Com_Users order by UserID desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY;


        Select "UserID" = @User, 
               Com_UserAllRights.UserRightGUID, 
               Com_UserAllRights.KeyName, 
               Com_UserAllRights.SubKey, 
               Com_UserAllRights.RightName, 
               Com_UserAllRights.[Description],
               "Approved" = (Select Count(*) 
                             FROM Com_UserApprovedRights 
                             WHERE Com_UserApprovedRights.UserGUID = com_User.UserGUID 
                             AND Com_UserApprovedRights.UserRightGUID = Com_UserAllRights.UserRightGUID),
               "GroupApproved" = (Select Count(*) 
                                  FROM Com_UserGroupApprovedRights 
                                  WHERE Com_UserGroupApprovedRights.GroupGUID = com_user.GroupGUID
                                  AND Com_UserGroupApprovedRights.UserRightGUID = Com_UserAllRights.UserRightGUID )
        From Com_UserAllRights LEFT OUTER JOIN
            Com_UserApprovedRights as ApprovedRights on Com_UserAllRights.UserRightGUID = ApprovedRights.UserRightGUID Left Outer Join
            (SELECT * FROM Com_users WHERE UserID = @User) Com_User On approvedrights.UserGUID = com_User.UserGUID
    ORDER BY UserID, KeyName, SubKey
    END
END
GO

Upvotes: 0

Views: 86

Answers (2)

AXMIM
AXMIM

Reputation: 2472

Here is the solution with the table variable.

Alter PROCEDURE proc_GetAllComUserRights
AS
DECLARE @Result AS TABLE(   UserID      NVARCHAR(50),
                            UserRightGUID   UNIQUEIdentifier,
                            KeyName         NVARCHAR(50),
                            SubKey          NVARCHAR(50),
                            RightName       NVARCHAR(50),
                            Approved        INT,
                            GroupApproved   INT)

DECLARE @User as nvarchar(50)
DECLARE @RowCount as int
select @Rowcount=count(*) from Com_Users;
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    -- Insert statements for procedure here

    While (@Rowcount > 0)
    BEGIN
        Select @Rowcount = @Rowcount - 1;
        Select @User = UserID FROM Com_Users order by UserID desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY;

        INSERT INTO @Result
        Select "UserID" = @User, 
               Com_UserAllRights.UserRightGUID, 
               Com_UserAllRights.KeyName, 
               Com_UserAllRights.SubKey, 
               Com_UserAllRights.RightName, 
               Com_UserAllRights.[Description],
               "Approved" = (Select Count(*) 
                             FROM Com_UserApprovedRights 
                             WHERE Com_UserApprovedRights.UserGUID = com_User.UserGUID 
                             AND Com_UserApprovedRights.UserRightGUID = Com_UserAllRights.UserRightGUID),
               "GroupApproved" = (Select Count(*) 
                                  FROM Com_UserGroupApprovedRights 
                                  WHERE Com_UserGroupApprovedRights.GroupGUID = com_user.GroupGUID
                                  AND Com_UserGroupApprovedRights.UserRightGUID = Com_UserAllRights.UserRightGUID )
        From Com_UserAllRights LEFT OUTER JOIN
            Com_UserApprovedRights as ApprovedRights on Com_UserAllRights.UserRightGUID = ApprovedRights.UserRightGUID Left Outer Join
            (SELECT * FROM Com_users WHERE UserID = @User) Com_User On approvedrights.UserGUID = com_User.UserGUID
    ORDER BY UserID, KeyName, SubKey
    END

    SELECT * FROM @Result
END

Upvotes: 1

Ajay
Ajay

Reputation: 6590

Use DataSet.Merge. it Merges a specified DataTable and its schema into the current DataSet.

Upvotes: 0

Related Questions