ios - Azure 移动服务自定义 API 多次调用 SQL SP

Question

我有一个带有自定义 API 的 Azure 移动服务，它似乎正在进行多个 SQL 调用。当我查看日志时，我看到 SP 返回了多个响应。其中一些是空返回(无记录集)，而其中之一与 SP 正确通信并返回记录集。

我知道我的 iOS 应用程序仅调用自定义 API 一次。

这是自定义 API:

exports.post = function(request, response) {
    var mssql = request.service.mssql; 

    var params = [request.query.FirstName , 
    request.query.LastName , 
    request.query.DOB ,
    request.query.EmailAddress , 
    request.query.PhoneNumber , 
    request.query.FacilityID ,
    request.query.DiagnosisID, 
    request.query.GeneratedBy , 
    request.query.UserTypeID];

    console.log("processregistration params = '%j'", params);
    var sql = "exec MyStoredProcName ?, ?, ?, ?, ?, ?, ?, ?, ?";
    mssql.query(sql, params, {
        success: function(results) {
            console.log("results = '%j'", results);
            // I had to put this in there to keep it from returning an empty recordset
            // back to my iOS app
            if (results.length > 0) {
                response.send(statusCodes.OK, results);
            }

        }        
    });
};

这是日志:

我知道 SP 只生成一个记录集，并且测试它直接生成一个记录集。我已经能够创可贴，但在 response.send 之前添加了一个 if 语句。我以前从未这样做过。有任何想法吗？

编辑 - 这是我的 SP(为 carlosfigueira 发布)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*

==========================================================================================
Author:         LJ Wilson
Create date:    2013-12-07
Description:    Processes a new (or existing) user registration
==========================================================================================

Revision History
Date            Initials    Comments
2013-12-07      LJW         Created
2013-12-18      LJW         Fixed issue with InvitationCodeID

*/
ALTER PROCEDURE [MyApp].[sp_Process_Registration]
    @FirstName VARCHAR(500) ,
    @LastName VARCHAR(500) ,
    @DOB DATETIME ,
    @EmailAddress VARCHAR(500) ,
    @PhoneNumber VARCHAR(25) ,
    @FacilityID INT ,
    @DiagnosisID INT ,
    @GeneratedBy NVARCHAR(500) ,
    @UserTypeID INT
AS 

DECLARE @Timestamp AS DATETIME = CURRENT_TIMESTAMP

DECLARE @ExistingUserID AS INT = ( SELECT   ID
                                   FROM     MyApp.Users
                                   WHERE    UserTypeID = @UserTypeID
                                            AND EmailAddress = @EmailAddress
                                            AND LastName = @LastName
                                 )

DECLARE @EmailAddressInUse AS INT = ( SELECT    COUNT(ID)
                                      FROM      MyApp.Users
                                      WHERE     EmailAddress = @EmailAddress
                                    )  

IF @ExistingUserID IS NULL 
    BEGIN
        BEGIN TRAN
        INSERT  INTO MyApp.Users
                ( LoginName ,
                  FriendlyName ,
                  JoinDate ,
                  PhoneNumber ,
                  EmailAddress ,
                  UserTypeID ,
                  IsActive ,
                  FacilitiesListID ,
                  AllowTexting ,
                  AllowEmail ,
                  AllowCalls ,
                  AllowAPNS ,
                  ImagePath ,
                  ShowImage ,
                  LastUpdatedBy ,
                  LastUpdatedOn ,
                  FirstName ,
                  LastName ,
                  DOB
                )
        VALUES  ( @EmailAddress , -- LoginName - nvarchar(250)
                  NULL , -- FriendlyName - nvarchar(500)
                  @Timestamp , -- JoinDate - datetime
                  @PhoneNumber , -- PhoneNumber - nvarchar(50)
                  @EmailAddress , -- EmailAddress - nvarchar(500)
                  @UserTypeID , -- UserTypeID - smallint
                  1 , -- IsActive - bit
                  NULL , -- FacilitiesListID - int
                  NULL , -- AllowTexting - bit
                  1 , -- AllowEmail - bit
                  NULL , -- AllowCalls - bit
                  1 , -- AllowAPNS - bit
                  NULL , -- ImagePath - nvarchar(1000)
                  NULL , -- ShowImage - bit
                  @GeneratedBy , -- LastUpdatedBy - nvarchar(500)
                  @TimeStamp , -- LastUpdatedOn - datetime
                  @FirstName , -- FirstName - varchar(500)
                  @LastName ,  -- LastName - varchar(500)
                  @DOB -- DOB - datetime
                )
        COMMIT TRAN
        SET @ExistingUserID = ( SELECT  @@IDENTITY
                              )  
    END  

BEGIN
    DECLARE @InvitationCode AS VARCHAR(50) = ( SELECT   dbo.fnc_GenInvitationCode(@EmailAddress,
                                                              @FacilityID,
                                                              @DiagnosisID)
                                             )
    INSERT  INTO MyApp.InvitationCodes
            ( InvitationCode ,
              UserID ,
              FacilityID ,
              InvitationGeneratedOn ,
              GeneratedBy ,
              DiagnosisIdentifier ,
              InvitationActive

            )
    VALUES  ( @InvitationCode , -- InvitationCode - nvarchar(50)
              @ExistingUserID , -- UserID - int
              @FacilityID , -- FacilityID - int
              @Timestamp , -- InvitationGeneratedOn - datetime
              @GeneratedBy , -- GeneratedBy - nvarchar(500)
              @DiagnosisID , -- DiagnosisIdentifier - int
              NULL  -- InvitationActive - bit

            )
END
DECLARE @InvitationCodeID AS INT = ( SELECT ID
                                     FROM   MyApp.InvitationCodes
                                     WHERE  InvitationCode = @InvitationCode
                                            AND InvitationGeneratedOn = @Timestamp
                                   )

INSERT  INTO MyApp.FacilitiesList
        ( UserID ,
          InvitationCodeID ,
          FacilityID ,
          AddedOn ,
          AddedBy

        )
VALUES  ( @ExistingUserID , -- UserID - int
          @InvitationCodeID , -- InvitationCodeID - int
          @FacilityID , -- FacilityID - int
          @Timestamp , -- AddedOn - datetime
          @GeneratedBy  -- AddedBy - nvarchar(500)

        )

SELECT  @InvitationCode AS InvitationCode ,
        @EmailAddressInUse AS EmailAddressInUse ,
        @ExistingUserID AS ExistingUserID


GO

Answer 1

您遇到的问题是您的存储过程正在执行大量中间操作(查询现有用户 ID、查询正在使用的电子邮件地址等)，并且默认情况下所有这些操作都返回为SQL 服务器的结果集，这就是为什么您在“真实”结果集之前看到那些空结果集的原因。

您可以在存储过程中使用SET NOCOUNT ON语句，这将有效地从返回到mssql.query<的结果集列表中删除那些中间SQL执行 函数。你的 SPROC 看起来像这样

ALTER PROCEDURE [carlosfigueira].[sp_Process_Registration]
    @FirstName VARCHAR(500) ,
    @LastName VARCHAR(500) ,
    @DOB DATETIME ,
    @EmailAddress VARCHAR(500) ,
    @PhoneNumber VARCHAR(25) ,
    @FacilityID INT ,
    @DiagnosisID INT ,
    @GeneratedBy NVARCHAR(500) ,
    @UserTypeID INT
AS 

SET NOCOUNT ON

DECLARE @Timestamp AS DATETIME = CURRENT_TIMESTAMP

-- rest of the stored proc code is the same

添加该语句后，传递给 mssql.query 的回调将仅被调用一次。