PHP and SQL Server Stored Procedures, how to insert a new record under 4 steps

Having SQL Server storing data for your PHP web application isn’t a common combination but is a far better option than having MySQL when it comes to larger database. I wont brag about why choose SQL Server over MySQL instead will quickly demonstrate in few steps how to execute stored proc in PHP using mssql database extension library.

We will be using mssql_query to execute the stored procedure and return records added using SCOPE_IDENTITY(). Please accept my appologies for coding indentation and lack colouring! @@

Let’s take a task list scenario where we manage our to-do tasks in a table having an ID, name, date added, priority task, and active fields.

First step, create your table in SQL server like the following:

Step 2, Create a stored procedure to insert records:

CREATE proc [dbo].[usp_InsertTask]
(
@TaskName varchar(255),
@TaskDateAdded datetime,
@TaskPriority int,
@TaskActive bit
)
as
insert into tblTask(TaskName, TaskDateAdded, TaskPriority, TaskActive)
values (@TaskName, @TaskDateAdded, @TaskPriority, @TaskActive)
SELECT SCOPE_IDENTITY() as id
RETURN

Note “SELECT SCOPE_IDENTITY() as id” will return the inserted record TaskID set as auto increment.

Check out Pinal Dave, SQL Server MVP, blog on the differenced between SCOPE_IDENTITY() vs @@IDENTITY vs IDENT_CURRENT to retrieve the last inserted record.

Step3, back to PHP to create your mssql_connect connection and execute stored procedure

<?php
$myServer = “10.64.0.7”;
$myUser = “mydevuser”;
$myPass = “mydevpassword”;
$myDB = “devdatabase”;

// connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die(“Couldn’t connect to SQL Server on $myServer”);

// select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die(“Couldn’t open database $myDB”);

//setup variable data
$TaskName = ‘Configure SQL Server’;
$TaskDateAdded = ’12/22/2008′;
$TaskPriority = 2;
$TaskActive = true;

$result = mssql_query(“usp_InsertTask ‘$TaskName’, ‘$TaskDateAdded’, ‘$TaskPriority’, ‘$TaskActive'”, $dbhandle);
OR
$result = mssql_query(“EXEC dbo.usp_InsertTask @TaskPriority = ‘$TaskPriority’, @TaskName = ‘$TaskName’, @TaskDateAdded = ‘$TaskDateAdded’, @TaskActive = ‘$TaskActive'”, $dbhandle);
$result = mssql_fetch_object($result);
echo “Record ID = “.$result->id;

// close the connection
mssql_close($dbhandle);
?>

Step 4, DONE!

Most blog posts found when google-ing php and stored procedures SQL Server will go on about using mssql_execute function instead of mssql_query directly. But you will find much more posts about how it actually doesn’t work as php.net/mssql manual suggested. Probably because of new version of SQL server? PHP? or wrong mssql driver or configuration?

The steps i have provided is working under PHP5 on a Debian dev server and SQL Server 2008 CTP.