在PHP中创建的多行批处理命令无法识别我声明的变量

Question

My php class creates the following command:

INSERT INTO contacts (firstName, lastName) VALUES ('jon', 'snow') 
DECLARE @IDcontacts VARCHAR(200)
SET @IDcontacts = @@IDENTITY;

INSERT INTO emailAddresses (ownerId, emailAddress) VALUES (@IDcontacts, '[e-mail]') 
INSERT INTO emailAddresses (ownerId, emailAddress) VALUES (@IDcontacts, '[e-mail]') 

INSERT INTO emailAddresses (ownerId, emailAddress) VALUES (@IDcontacts, '[e-mail]') 

I create the connection, begin a transaction with, and execute the batch with sqlsrv_query().

After executing the batch, sqlsrv_errors() gives me four errors: [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@IDcontacts".

however, if I use exactly the same batch string in SQL Server Management Studio, it works fine and all four records are inserted with the correct identity value from the first insert.

I'm using SQL Server Express 2005, php version 5.4.3 and the most recent sqlsrv driver. I haven't been able to find anything about this on technet or the ms forums, and any help would be appreciated.

Answer 1

In essence, as described in a response to this question on the MSDN forums, the SQL is not executed as a batch even when submitted as a single string. Each command is executed by sp_executesql, which makes every command run in a separate scope.

I suppose I could use a global temporary table to do the same thing, but instead chose to rewrite the PHP to handle each command separately, handling transactions, errors, and backreferences after each command is attempted. I was hoping that wasn't true, but I am told it is.