dorpbn1027 2014-01-03 19:05
浏览 62
已采纳

如何调试错误:1038,严重级15,状态:4从PHP执行SP时

When I execute a Store Procedure from PHP

$days = 30;
$stmt = mssql_init('[dbo].[DEVDB_sp_GET_Overpayment_By_Provider]');
mssql_bind($stmt, '@Days', $days, SQLINT4);
$result = mssql_execute($stmt);
mssql_free_statement($stmt);
$array = array();
while ($row = mssql_fetch_array($result, MSSQL_NUM)) {
   $array[] = $row;
}

I get error:1038, Severity 15, State: 4 in the SQL Profiler

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. (severity 15)

If I run this Sp from SQL, just run without any problem

This the [dbo].[DEVDB_sp_GET_Overpayment_By_Provider] Code

@FromDate DATE = NULL,
@ToDate DATE = NULL,
@Days INT = 0
AS
BEGIN
        SET NOCOUNT ON;

        --Start : Calculate the from date and to date based on the specified days
        IF(@Days > 0 AND @FromDate IS NULL AND @ToDate IS NULL)
        BEGIN 
                SET @FromDate = CONVERT(DATE,DATEADD(DAY,-@Days,GETDATE()))
                SET @ToDate = CONVERT(DATE,GETDATE())
        END 
        --End

        --Start : Select top 5 providers with highest overpayment
        SELECT 
                        RES.ProvID,RES.ProvName,R_L.LOB,SUM(PPI.PIyAmt) AS [OverPAmt]
        FROM 
                        DBO.DEVDB_CH PCH 
                        INNER JOIN DBO.DEVDB_PIy PPI ON PCH.ClientCHID = PPI.ClientCHID
                        INNER JOIN (SELECT  TOP 5 
                                                                P_CH.ProvID,
                                                                P_PROV.ProvName
                                                FROM    
                                                                DBO.DEVDB_Prov P_PROV WITH(NOLOCK)
                                                                INNER JOIN DBO.DEVDB_CH P_CH WITH(NOLOCK) ON P_PROV.ProvID = P_CH.ProvID
                                                                INNER JOIN DBO.DEVDB_PIy  P_PI WITH(NOLOCK) ON P_CH.ClientCHID = P_PI.ClientCHID
                                                WHERE   
                                                                (P_PI.DatePROVNotified1st BETWEEN @FromDate AND @ToDate) AND
                                                                P_PI.PIyAmt > 0
                                                GROUP BY 
                                                                P_CH.ProvID,P_PROV.ProvName
                                                ORDER BY
                                                                SUM(P_PI.PIyAmt) DESC) AS [RES] ON PCH.ProvID = RES.ProvID
                        INNER JOIN DBO.REF_LOB_tbl R_L  ON PCH.LOBID = R_L.LOBID                    
        WHERE 
                        (PPI.DatePROVNotified1st BETWEEN @FromDate AND @ToDate) AND
                         PPI.PIyAmt > 0                     
        GROUP BY
                        R_L.LOB,RES.ProvID,RES.ProvName
        ORDER BY 
                        RES.ProvID
        --End
END

The result Set is

Result set whe SP is execute from SQL

How can I debug this kind of error.

I note each time I got this kind of error is when exist NULL Values

  • 写回答

1条回答 默认 最新

  • donglei3370 2014-01-04 02:20
    关注

    The problem was solved binding the other two SQL parameter and specifying is_null parameter in mssql_bind

    $days= 30;
    $FromDate = NULL;
    $ToDate = NULL;
    mssql_bind($stmt, '@Days', $days, SQLINT4,FALSE,FALSE);
    mssql_bind($stmt, '@FromDate ', $FromDate , SQLVARCHAR,FALSE,TRUE);
    mssql_bind($stmt, '@ToDate', $ToDate, SQLVARCHAR,FALSE,TRUE);
    

    I don't know why I have to bind the other two parameters if in the SQL I have

    IF(@Days > 0 AND @FromDate IS NULL AND @ToDate IS NULL)

    But it works, would be nice someone with the knowledge explain in more details this topic.

    Special Thanks to @AndriyM and @ypercube for guide trough the process.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?