如何使用存储过程SQL SERVER 2008 R2（mssql）插入PHP数组值 [英] How to insert PHP array values using an stored procedure SQL SERVER 2008 R2(mssql)

问题描述

我有这个数组

$REV = Array
(
    0 => 240,
    1 => 241,
    2 => 242,
    3 => 243,
    4 => 249
);

我现在使用下面的代码插入，将每个数组的元素存储在一行中$ id，$ userID，类型和日期

and i'm using this code bellow to insert for now, stored each array's element in a row with $id, $userID, Type and Date

if (!empty($REV)) {
    foreach ($REV as $val_rev) {
        $values_rev[] = "('" . $ID . "','REV','" . $val_rev . "','" . $IDUSER . "',GETDATE())";
    }
    $values_rev_insert = implode(',', $values_rev);

    $query_rev = "insert into dbo.CCLine (ID,CCType,CSID,IdUSer,DateCreated)values" . $values_rev_insert;
    mssql_query($query_rev);
}

但是我想要的是可以使用此存储过程，但是我不知道如何

But what i want is can use this stored procedure but i dont have idea how to make to insert in one time using the sp:

$stmt = mssql_init('sp_insertRev');
mssql_bind($stmt, '@ID', $ID, SQLINT4);
mssql_bind($stmt, '@CCType', 'REV', SQLVARCHAR);

数组在这里不起作用

mssql_bind($stmt, '@CSID', $val_rev, SQLINT4);//An example 

---

---

mssql_bind($stmt, '@IdUSer', $IDUSER, SQLCHAR);
$result = mssql_execute($stmt);

如何在阵列中使用此SP

How can i use this SP with the array

CREATE PROCEDURE [dbo].[sp_HCCInsert]
            @ID int
           ,@CCType varchar(10)
           ,@CSID varchar(10)
           ,@IdUSer char(15)

AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @CCID as INT

    INSERT INTO [dbo].[CCLine]
           ([ID]
           ,[CCType]
           ,[CSID]
           ,[IdUSer]
           ,[DateCreated])
     VALUES
           (@ID
           ,@CCType
           ,@CSID
           ,@IdUSer
           ,GETDATE())


      SET @CCID = @@IDENTITY

      Select @CCID as CCID


END

推荐答案

我已经在此帖子中找到了解决问题的方法

I've found solution to your problem in this post

所有有关将数组作为XML字符串传递给过程的过程，并在过程中将其用于带有OPENXML函数的INSERT SELECT语句中。

It's all about passing the array as XML string which is passed to the procedure and in procedure it is used in INSERT SELECT statement with OPENXML function.

CREATE PROCEDURE [dbo].[sp_HCCInsert]
(
   @XMLDoc XML
)

然后使用功能< MSSQL中的code> OPENXML 。您应该阅读本主题。
因此伪代码看起来像

Then use function OPENXML in MSSQL. You should read this topic. So pseudo code will look like

INSERT ... SELECT OPENXML（@XML ...）

阅读并适合您的需求后，只需将XML传递给过程即可。

After you read it and fit to your needs just pass XML to procedure.

关于OPENXML的一些有用链接

Some useful links about OPENXML

• http://msdn.microsoft.com/pl-pl/library/ms175160.aspx


• 在SQL Server 2008存储的过程中使用OPENXML-插入顺序与XML文档不同


• http://www.informit.com/articles/article.aspx?p=26499

• http://msdn.microsoft.com/pl-pl/library/ms175160.aspx
• Using OPENXML in SQL Server 2008 stored proc - INSERT order differs from XML document
• http://www.informit.com/articles/article.aspx?p=26499

此外，我建议使用PDO，因为它具有更好的抽象层。希望对您有所帮助。

Moreover, I'd suggest using PDO because it has better abstract layer. I hope that it helped you.

这篇关于如何使用存储过程SQL SERVER 2008 R2（mssql）插入PHP数组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！