Combination of 2 SQL statements in 1

I need to combine the following two SQL statements into one. Any help is greatly appreciated.

Thanks.

SELECT C.*, M.members_Email
FROM tbl_Campaigns C
JOIN tbl_Members M
ON C.campaign_MemberId = M.members_Id
WHERE C.campaign_MemberId = @userID
ORDER BY C.campaign_Key DESC

SELECT COUNT(*) FROM tbl_CampaignRecipients
WHERE recip_CampaignId = C.campaign_Key AND
(recipient_Status = 3 or recipient_Status = 4)

However, i need to return the results from statement 1 even if no results are present for statement 2.

Why do you need to combine the two statements into one? However you manage to accomplish that, you will inflate the size of the resultset being passed over the network unnecessarily.

I suggest combining the two statements into a stored procedure instead:

CREATE PROCEDURE GetAllData (@NumberOfRecipients int output) AS BEGIN

SELECT C.*, M.members_Email
FROM tbl_Campaigns C
JOIN tbl_Members M ON C.campaign_MemberId = M.members_Id
WHERE C.campaign_MemberId = @userID
ORDER BY C.campaign_Key DESC

SELECT @NumberOfRecipients = COUNT(*)
FROM tbl_CampaignRecipients
WHERE recip_CampaignId = C.campaign_Key
AND (recipient_Status = 3 or recipient_Status = 4)

END

In your client-side code, you would call it like this:

  1. Create a command object for the GetAllData
    stored procedure.
  2. Attach a parameter object for the @NumberOfRecipients
    parameter.
  3. Execute the command.
  4. Consume the default resultset.
  5. Read the @NumberOfRecipients
    parameter value (this must
    be done after consuming the resultset).

C# example:

using(SqlCommand command = new SqlCommand("GetAllData", connection)) {
    command.CommandType = CommandType.StoredProcedure;
    SqlParameter recipientsParam = new SqlParameter("@NumberOfRecipients", SqlDbType.Int) { Direction = ParameterDirection.Output };
    command.Parameters.Add(recipientsParam);
    using(SqlDataReader reader = command.ExecuteReader()) {
        // consume the resultset
    }
    // read the parameter
    int recipients = (int) recipientsParam.Value;
}

You can also re-use the stored procedure in server-side T-SQL code, e.g.:

declare @NumberOfRecipients int
insert into #Results execute GetAllData @NumberOfRecipients output
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Combination of 2 SQL statements in 1

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录