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
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
Moreover, I'd suggest using PDO because it has better abstract layer. I hope that it helped you.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With