Executing SQL Server Stored Procedure from PHP

Difference between PHP on Windows and PHP on Linux is:

On windows, the MS SQL Server support module is running as a DLL file. In order to enable the extension, must uncomment the line from the php.ini file : extension=php_mssql.dll

On Linux, the MS SQL Server support module is compiled into libphp5.so, there is no need to load it from extensions.


Execute Microsoft SQL Server stored procedures from PHP by calling the following functions: mssql_connect(), mssql_bind(), mssql_select_db(), mssql_execute(), mssql_init(), mssql_free_statement and mssql_close().

Syntax
mssql_connect (string $servername, string $username, string $password, bool $new_link)

mssql_connect() establishes a connection to a MS SQL server. The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mssql_close().

$new_link - If a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. This parameter modifies this behavior and makes mssql_connect() always open a new link, even if mssql_connect() was called before with the same parameters.

Returns a MS SQL link identifier on success, or FALSE on error.

"Warning: mssql_connect() [function.mssql-connect]: message: Cannot open user default database. Using master database instead. (severity 11) in "

It means the default database for the user you are connecting with doesn't exist or you don't have permission to access it. To ignore the warning
replace mssql_connect($server, $username, $password); code to @mssql_connect($server, $username, $password);.

Accessing SQL Query and Stored Procedure from the pubs database:

Example 1

CREATE PROCEDURE [dbo].[spEmployee] AS
SELECT fname FROM employee;
GO

<?php
$server = "localhost,1433";
$username = "sa";
$password = "pwd";
$databasename = "pubs";

$sqlconnect = @mssql_connect($server, $username, $password) or die("Couldn't connect to SQL Server");
$sqldb = mssql_select_db($databasename,$sqlconnect) or die("Couldn't open database");

// Using SQL Query
$sqlquery = "SELECT fname FROM employee;";
$results = mssql_query($sqlquery);
while ($row=mssql_fetch_array($results))
{
echo $row['fname']."
\n";
}

//using stored procedure
$sp = mssql_init("spEmployee", $sqlconnect) or die("Couldn't call stored procedure");
$results= mssql_execute($sp) or die(mssql_get_last_message());
while ($row=mssql_fetch_array($results))
{
echo $row['fname']."
\n";
}
mssql_close($sqlconnect);
?>

Example 2

CREATE PROCEDURE [dbo].[spGetEmployee]
@sTitleID varchar(10),
@sBookTitle varchar(100) OUTPUT
AS
DECLARE @iTotalEmployee int
SELECT @sBookTitle =title FROM titles WHERE title_id = @sTitleID
SELECT @iTotalEmployee = COUNT(emp_id) FROM employee
RETURN @iTotalEmployee
GO

<?php
$sp = mssql_init("spGetTitle_TotalEmployee", $sqlconnect) or die("Couldn't call stored procedure");

$sTitleID = 'bu1032';
$iTotalEmployee = 0;
$sBookTitle = '';

// Bind the parameters
mssql_bind($sp, "@sTitleID", $sTitleID, SQLVARCHAR);
mssql_bind($sp, "@sBookTitle", $sBookTitle, SQLVARCHAR, TRUE, FALSE,100);

// Bind the return value
mssql_bind($sp, "RETVAL", $iTotalEmployee, SQLINT2);

mssql_execute ($sp);
mssql_free_statement ($sp);
mssql_close($sqlconnect);

echo "There were $iTotalEmployee Employees returned.";
echo "The book title was: $sBookTitle.";
?>