How to insert PHP array values using an stored procedure SQL SERVER 2008 R2(mssql)

Question

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

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);

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

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

Answer 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.

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

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

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

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

Some useful links about OPENXML

• 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

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

Answer 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.

$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.

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.