最受欢迎

用PHP运行SQL存储过程

Running SQL Stored Procedure in PHP

本文关键字：存储过程 SQL 运行 PHP | 更新日期: 2023-09-27

我不熟悉PHP，但我试图找出为什么这是不工作。我试图运行一个SQL存储过程，它通过php接受一些参数。这段代码应该在表单中添加额外的一行，以便根据标准输入用户数据。当我直接在SQL Server Management Studio中运行存储过程时，它工作得很好，并将行添加到后端表中，并反映前端表单中的行，但是当尝试使用底层代码在前端添加行时，我没有得到任何错误，也没有更新。

我不熟悉PHP，但我试图学习和找出为什么这是不工作。如有任何帮助，不胜感激。

下面是底层PHP代码。

<?PHP include 'Connections/PromAPPS01Connection.php'; ?>
<?php
session_start();
$sUserid = "9999";
if(isset($_SESSION['sUserID'])){
    $sUserid = $_SESSION['sUserID'];
    //echo "Yes";
}
else{
    /* Redirect browser */
    header("Location: LoginSession.php");
    /* Make sure that code below does not get executed when we redirect. */
    exit;
    //echo "No";
}
$sPostPropertyId = $_POST["Props"];
$sPostGroupId = $_POST["AcctGroup"];
$sRecordStart = $_POST["RS"];
$sPostAcct = $_POST["Acct"];
$sPostYear = $_POST["Year"];
$sBudgetID = $_POST["BudgetID"];
$sPostRowCount = $_POST["AcctCount"];
$sSubmitType = $_POST["SubmitType"];
$sView = $_POST["View"];
$sAddLineAcct = $_POST["NewLineAcct"];
$sAddLineProp = $_POST["NewLineProp"];
$sAddLineYear = $_POST["NewLineYear"];
$sPropertyNumber = $_POST["PropertyNumber"];
$sType = $_POST["Type"];
$sDept = $_POST["Dept"];
$sComments = $_POST["Comments"];
$sAddType = $_POST["AddType"];
$sqlNewLine = "Exec Budgeting.procAddNewLines '". $sPostPropertyId ."', '". $sPropertyNumber ."', '". $sAddLineAcct ."', '". $sAddType ."','". $sType ."', '". $sAddLineYear ."', '". $sPostYear ."', '". $sComments ."', '". $sUserid ."','". $sDept ."' ";
//echo "<br>";
//echo $sAddType ." & ". $sAddLineYear . " &". $sPostYear;
if ($sRecordStart == ""){ 
    $sRecordStart = 0;
}
$iNextRS = $sRecordStart;
$navigateLocation = "PlanSheetProperty.php";
//echo "<br>";
//  echo $sqlNewLine;
    // Executing the sql update code
        //conn.Execute sql_New_Line
    //#####################################################################
    $stmtNewLine = sqlsrv_prepare($dbLink, $sqlNewLine);
    sqlsrv_execute($stmtNewLine);
            //#################################
?> 
<html>
<head>
<Link Rel="Stylesheet" href="RenewStyle.css" type="text/css">
    <title>The Budget Machine</title>
</head>
<!--body-->
<body onLoad="document.AcctUpdate.submit();">
<?PHP include 'Top.php'; ?>
<table width="100%" bordercolor="#003366" cellpadding="0" cellspacing="0"><tr><td>
<table width="100%" cellpadding="3" cellspacing="0" border="0">
    <tr class="Title1">
        <td valign="bottom">&nbsp;
        </td>
        <td colspan="5" rowspan="2" valign="middle" nowrap>&nbsp;
        </td>
        <td colspan="13" align="right" valign="bottom" nowrap>&nbsp;
        </td>
        <td align="right" valign="bottom" nowrap>&nbsp;
        </td>
        <td align="right" valign="bottom" nowrap>&nbsp;
        </td>
        <td align="right" valign="bottom" nowrap>
        <!--input type="Button" value="< Back to Properties" onClick="document.location='PropertyList.asp'"-->
        </td>
        <td valign="bottom">&nbsp;
        </td>
    </tr>
    <tr class="Title1">
        <td valign="bottom">&nbsp;
        </td>
        <td colspan="5" rowspan="2" valign="middle" nowrap>&nbsp;
        </td>
        <td colspan="13" align="left" valign="bottom" nowrap>
        Saving data...<br>
        Please be patient
        </td>
        <td align="right" valign="bottom" nowrap>&nbsp;
        <form method="post" action="<?php echo $navigateLocation ?>" name="AcctUpdate" id="AcctUpdate" />        
        <input type="hidden" name="RS" id="RS" value="<?PHP echo $iNextRS ?>">
        <input type="hidden" name="Props" id="Props" value="<?PHP echo $sPostPropertyId ?>">
        <input type="hidden" name="AcctGroup" id="AcctGroup" value="<?PHP echo $sPostGroupId ?>">
        <input type="hidden" name="GLAcct" id="GLAcct" value="<?php echo $sAddLineAcct ?>">
        <input type="hidden" name="AcctCount" id="AcctCount" value="<?PHP echo $sPostRowCount ?>">
        <input type="hidden" name="BudgetID" id="BudgetID" value="<?PHP echo $sBudgetID ?>">
        <input type="hidden" name="NewLineAcct" id="NewLineAcct" value="<?php echo $sPostAddLineAcct ?>">        
        <input type="hidden" name="PropertyNumber" id="PropertyNumber" value="<?php echo $sPostPropertyNumber ?>">
        <input type="hidden" name="SubmitType" id="SubmitType" value="<?php echo $sSubmitType ?>">
        <input type="hidden" name="View" id="View" value="<?php echo $sView ?>">
        <input type="hidden" name="NewLineProp" id="NewLineProp" value="<?php echo $sPostAddLineProp ?>">
        </form>
        </td>
        <td align="right" valign="bottom" nowrap>&nbsp;
        </td>
        <td align="right" valign="bottom" nowrap>&nbsp;
        </td>
        <td align="right" valign="bottom" nowrap>
        </td>
        <td valign="bottom">&nbsp;
        </td>
    </tr>
     </table>
</td></tr></table>
</body>
</html>
下面是存储过程: 
 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure AddNewLines
(
    @PropertyID numeric (10,0) = NULL,
    @PropertyNumber varchar (30) = NULL,
    @GLAccountNumber varchar (10) = NULL,
    @InsertType varchar (10) = NULL,
    @LineAssoc varchar (10) = NULL, 
    @ClickYear varchar (30) = NULL,
    @Year varchar (30) = NULL,
    @Comments varchar (800) = NULL,
    @User int = NULL,
    @DeptCode varchar (10) = NULL
) AS


IF @InsertType = 'Single'
    BEGIN
    IF @LineAssoc = 'Project'
        BEGIN
        INSERT INTO BudDat(PropertyID, 
            PropertyNumber, 
            GLAccountNumber, 
            Project, 
            [Year], 
            BudgetID, 
            Comments, 
            LastChangedDate, 
            LastChangedBy) 
            VALUES (@PropertyID, 
            @PropertyNumber, 
            @GLAccountNumber, 
            'Y', 
            @ClickYear, 
            '', 
            @Comments, 
            getDate(), 
            @User)
        END

    IF @LineAssoc = 'None'
        BEGIN
        INSERT INTO BudDat(PropertyID, 
            PropertyNumber, 
            GLAccountNumber, 
            DeptCode, 
            [Year], 
            BudgetID, 
            Comments, 
            LastChangedDate, 
            LastChangedBy) 
            VALUES (@PropertyID, 
            @PropertyNumber, 
            @GLAccountNumber, 
            @DeptCode,
            @ClickYear, 
            '', 
            @Comments, 
            getDate(), 
            @User)
        END
    END
IF @InsertType = 'Multiple'
    BEGIN
    DECLARE @YearCount int
    Select @YearCount = 0
    IF @LineAssoc = 'Project'
        BEGIN
        WHILE (@YearCount < 5)
            BEGIN
            INSERT INTO BudDat(PropertyID, 
                PropertyNumber, 
                GLAccountNumber, 
                Project, 
                [Year], 
                BudgetID, 
                Comments, 
                LastChangedDate, 
                LastChangedBy) 
                VALUES (@PropertyID, 
                @PropertyNumber, 
                @GLAccountNumber, 
                'Y', 
                @Year, 
                '', 
                @Comments, 
                getDate(), 
                @User)
            Select @YearCount = @YearCount+1
            Select @Year = @Year+1
            END
        END

    IF @LineAssoc = 'None'
        BEGIN
        WHILE (@YearCount < 5)
            BEGIN
            INSERT INTO BudDat(PropertyID, 
                PropertyNumber, 
                GLAccountNumber, 
                DeptCode, 
                [Year], 
                BudgetID, 
                Comments, 
                LastChangedDate, 
                LastChangedBy) 
                VALUES (@PropertyID, 
                @PropertyNumber, 
                @GLAccountNumber, 
                @DeptCode,
                @Year, 
                '', 
                @Comments, 
                getDate(), 
                @User)
            Select @YearCount = @YearCount+1
            Select @Year = @Year+1
            END
        END
    END

我对PHP了解不多，但是你可以极大地简化这个存储过程，不需要循环之类的东西。这应该做同样的事情。

ALTER Procedure AddNewLines
(
    @PropertyID numeric (10,0) = NULL,
    @PropertyNumber varchar (30) = NULL,
    @GLAccountNumber varchar (10) = NULL,
    @InsertType varchar (10) = NULL,
    @LineAssoc varchar (10) = NULL, 
    @ClickYear varchar (30) = NULL,
    @Year varchar (30) = NULL,
    @Comments varchar (800) = NULL,
    @User int = NULL,
    @DeptCode varchar (10) = NULL
) AS
    IF @LineAssoc = 'Project'
        INSERT INTO BudDat(PropertyID, 
            PropertyNumber, 
            GLAccountNumber, 
            Project, 
            [Year], 
            BudgetID, 
            Comments, 
            LastChangedDate, 
            LastChangedBy) 
            select @PropertyID, 
            @PropertyNumber, 
            @GLAccountNumber, 
            'Y', 
            @ClickYear, 
            '', 
            @Comments, 
            getDate(), 
            @User
            from (VALUES (1),(2),(3),(4),(5)) as Numbers(x)
            where x <= case when @InsertType = 'Single' then 1 else 5 end

    IF @LineAssoc = 'None'
        INSERT INTO BudDat(PropertyID, 
            PropertyNumber, 
            GLAccountNumber, 
            DeptCode, 
            [Year], 
            BudgetID, 
            Comments, 
            LastChangedDate, 
            LastChangedBy) 
            SELECT @PropertyID, 
            @PropertyNumber, 
            @GLAccountNumber, 
            @DeptCode,
            @ClickYear, 
            '', 
            @Comments, 
            getDate(), 
            @User
            from (VALUES (1),(2),(3),(4),(5)) as Numbers(x)
            where x <= case when @InsertType = 'Multiple' then 1 else 5 end

顺便说一句……不太确定为什么它被标记为sql-server。看起来手术本身没什么问题。显然问题出在php

相关文章：
最新更新
www.56WenDa.com 2012-2023 | php问答网 | php学习