如何使用存储过程SQL SERVER 2008 R2（mssql）插入PHP数组值

I have this array

我有这个阵列

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

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

我现在使用此代码插入，将每个数组的元素存储在$ id，$ userID，Type和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:

但我想要的是可以使用这个存储过程，但我不知道如何使用sp一次插入：

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

The array does not work here

该阵列在这里不起作用

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

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

How can i use this SP with the array

我如何将此SP与阵列一起使用

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

3 个解决方案

#1

I've found solution to your problem in this post

我在这篇文章中找到了解决问题的方法

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.

所有这些都是关于将数组作为XML字符串传递给过程，在过程中它是在带有OPENXML函数的INSERT SELECT语句中使用的。

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

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

然后在MSSQL中使用函数OPENXML。你应该阅读这个主题。所以伪代码看起来像

INSERT ... SELECT OPENXML(@XML...)

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

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

在阅读完并满足您的需求后，只需将XML传递给过程即可。

Some useful links about OPENXML

一些关于OPENXML的有用链接

http://msdn.microsoft.com/pl-pl/library/ms175160.aspx
http://msdn.microsoft.com/pl-pl/library/ms175160.aspx
Using OPENXML in SQL Server 2008 stored proc - INSERT order differs from XML document
在SQL Server 2008中使用OPENXML存储过程 - INSERT命令与XML文档不同
http://www.informit.com/articles/article.aspx?p=26499
http://www.informit.com/articles/article.aspx?p=26499

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

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

#2

I tend to use PDO instead of the mssql_* functions, you can still use dblib and you can bind that array quite simply. One thing though, you have 5 elements in that array but only 4 input variables to your stored procedure. This example will assume you only have 4 elements in your array.

我倾向于使用PDO而不是mssql_ *函数，你仍然可以使用dblib，你可以非常简单地绑定该数组。但有一件事，你在该数组中有5个元素，但只有4个输入变量存储到你的存储过程中。此示例假设您的数组中只有4个元素。

$sql = "EXEC sp_HCCInsert ?, ?, ?, ?"; // SQL string with ? where params should go
$pdh = new PDO("dblib:host=somthing.somewhere\\INSTANCENAME;port=1433;dbname=MyDatabase;","username","password");
$sth = $pdh->prepare($sql); // Prepare query
$sth->execute($REV); // Execute and bind non-associative array

The only caution with using the PDO with dblib on PHP 5.3 or before, it goes and does a prefetch on execute. So if you execute a huge query and you want to loop through each record one at a time... sorry, you get the whole thing buffered first.

在PHP 5.3或之前使用PDO和dblib的唯一警告，它会在执行时执行预取。因此，如果您执行一个巨大的查询，并且您希望一次循环一个记录...抱歉，您可以先将所有内容缓冲。

Also, you can use an associative array if you'd like

此外，如果您愿意，可以使用关联数组

$REV = array(":ID" => 240, ":CCType" => 241, ":CSID" => 242, ":IdUSer" => 243);
$sql = "EXEC sp_HCCInsert :ID, :CCType, :CSID, :IdUSer"; // SQL string with named params
$pdh = new PDO("dblib:host=somthing.somewhere\\INSTANCENAME;port=1433;dbname=MyDatabase;","username","password");
$sth = $pdh->prepare($sql); // Prepare query
$sth->execute($REV); // Execute and bind associative array

The best part is, the execute method for the PDO::Statement escapes strings! Safety first you know.

最好的部分是，PDO :: Statement的execute方法转义字符串！安全第一，你知道。

#3

$sql  = "INSERT INTO table column(c1,c2) 
         VALUES('d1','d2'),('dd1','dd2'),('ddd1','ddd2')";// this is the basic sql command

$data = array('data1','data2','data3');
$data = '('.implode(', ',$data).')';
$sql  = "INSERT INTO table column(c1,c2) VALUES $data";

$data = array(array('1','2','3'),array('4','5','6'),array('7','8','9'));
$xa = array();
$a = 0;
foreach($data as $dt)
{
    $xa[$a] =   '('.implode(',',$dt).')';
    ++$a;
}
$data = '('.implode(',',$xa).')';
$sql  = "INSERT INTO table column(c1,c2) VALUES $data";

#1