How to pass an array to SQL Server using PHP

Question

Good day,

If I have the following code in SQL Server:

CREATE TYPE PartList
AS TABLE
(
    PartID varchar(30),
    Quantity int
);
GO

CREATE PROC spInsertPartList
@List AS PartList READONLY
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Part (PartID, Quantity)
    VALUES (SELECT PartID, Quantity FROM @List)

END
GO

Can I now call this procedure from PHP stating something like the following:

$array = array('PartNr123' => 50, 'PartNr25' => 4);
$stmt = mssql_init('spInsertPartList');
mssql_bind($stmt,'@List', &$array, PartList, false,  false);
mssql_execute($stmt);

I found the code for the SQL statements here, but unfortunately the code example works with C# DataTable.

EDIT:

Furthermore I am getting the following errors when executing the SQL script:

Msg 2715, Level 16, State 3, Procedure InsertStockTake, Line 194
Column, parameter, or variable #1: Cannot find data type dbo.CallPartList

And also this:

Msg 1087, Level 16, State 1, Procedure InsertStockTake, Line 200
Must declare the table variable "@partList".

Which is caused by the following code:

CREATE PROC dbo.InsertStockTake
@partList AS dbo.CallPartList READONLY
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
            DECLARE @partNr varchar(15), @qtyCount smallint, @qtyDB smallint
            DECLARE curStock CURSOR FOR (SELECT PartNr, Qty FROM @partList)
            OPEN curStock
            FETCH NEXT FROM curCallPart INTO @partNr, @qtyCount
            WHILE @@FETCH_STATUS = 0
                BEGIN           
                    SELECT @qtyDB = StockOH FROM dbo.Stock WHERE PartNr = 
                       @partNr
                    INSERT INTO scArchive.StockLevel(PartNr, 
                       StockOHDatabase, StockOHCounted, DateCaptured) 
                        VALUES (@partNr, @qtyDB, @qtyCount, GETDATE())
                    FETCH NEXT FROM curCallPart INTO @partNr, @qtyCount
                END
            CLOSE curStock
            DEALLOCATE curStock
        COMMIT
    END TRY
    BEGIN CATCH
        THROW;
        ROLLBACK
    END CATCH
END
GO

Thank you in advance

One thing to note; your table type is called `PartList`, but your proc accepts a `dbo.CallPartList`. That's probably why you're getting the `Cannot find data type dbo.CallPartList` error — Xedni, Sep 18 '17 at 18:21
Hi, yes that was a mistake on the edit. They are actually both called dbo.CallPartList in the actual script — Joandre Tait, Sep 18 '17 at 20:56
`mssql_*` functions are ancient, deprecated for years, and removed from the last two versions of PHP. Update your code! — miken32, Sep 18 '17 at 21:50
And you should rework your code to accept regular parameters; you can't pass the custom datatype from PHP. — miken32, Sep 18 '17 at 23:10
Thanks. That was not actual code in php. That was code I got on the PHP Manual site and asked only if one could use it like that. Although I am no longer trying to access MS SQL Svr, I went back to MySQL using mysqli. Thanks though — Joandre Tait, Sep 23 '17 at 17:55

score 0 · Accepted Answer · answered Sep 23 '17 at 17:58

0

No needed for this question any more. I used mysqli_* in php and adjusted my table structure and moved to MySQL rather than MS SQL

answered Sep 23 '17 at 17:58

Joandre Tait

21
1
7

How to pass an array to SQL Server using PHP

1 Answers1