Home arrow PHP arrow Page 5 - Executing SQL Server Stored Procedures With PHP
PHP

Executing SQL Server Stored Procedures With PHP


In this article Joe looks at how to connect to an SQL Server 2000 database using PHP's set of mssql_xxx functions, and also how to execute commands and stored procedures against that database.

Author Info:
By: Joe O'Donnell
Rating: 5 stars5 stars5 stars5 stars5 stars / 93
January 01, 2003
TABLE OF CONTENTS:
  1. · Executing SQL Server Stored Procedures With PHP
  2. · Configuring PHP
  3. · Running the mssql_xxx functions
  4. · Executing stored procedures
  5. · Capturing output parameters and return values
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Executing SQL Server Stored Procedures With PHP - Capturing output parameters and return values
(Page 5 of 6 )

It's just as easy to use output parameters and capture return values from stored procedures. Run the following code in query analyzer to create a stored procedure that accepts both input and output values and also returns a result:

USE Northwind

GO

CREATE PROC sp_GetNumProdsByPrice

@minPrice MONEY,

@maxPrice MONEY,

@lowestPricedProduct VARCHAR(40) OUTPUT,

@highestPricedProduct VARCHAR(40) OUTPUT

AS

SELECT @lowestPricedProduct = (SELECT TOP 1 ProductName

FROM Products

WHERE UnitPrice >= @minPrice

ORDER BY UnitPrice ASC)

SELECT @highestPricedProduct = (SELECT TOP 1 ProductName

FROM Products

WHERE UnitPrice <= @maxPrice

ORDER BY UnitPrice DESC)

RETURN (SELECT COUNT(*) FROM Products WHERE UnitPrice >= @minPrice AND UnitPrice <= @maxPrice)


Our stored procedure works with the products table of the Northwind database and is called sp_GetNumProdsByPrice. It accepts two input and two output parameters. The input parameters are the minimum and maximum price of the item (UnitPrice) to match. The output parameters will contain the names of the products whose UnitPrice field was close to @minPrice and @maxPrice respectively.

Now, onto the PHP code. Create a new file called prodsbyprice.php and enter the following code into it:

<?php

$myServer = "localhost";

$myUser = "sa";

$myPass = "";

$myDB = "Northwind";

$s = @mssql_connect($myServer, $myUser, $myPass)

or die("Couldn't connect to SQL Server on $myServer");

$d = @mssql_select_db($myDB, $s)

or die("Couldn't open database $myDB");

$query = mssql_init("sp_GetNumProdsByPrice", $s);

$minPrice = 0.00;

$maxPrice = 35.00;

$lowProd = "";

$highProd = "";

$numProds = 0;

// Bind the parameters

mssql_bind($query, "@minPrice", $minPrice, SQLFLT8);

mssql_bind($query, "@maxPrice", $maxPrice, SQLFLT8);

mssql_bind($query, "@lowestPricedProduct", &$lowProd, SQLVARCHAR, TRUE, FALSE, 40);

mssql_bind($query, "@highestPricedProduct", &$highProd, SQLVARCHAR, TRUE, FALSE, 40);

// Bind the return value

mssql_bind($query, "RETVAL", &$numProds, SQLINT2);

mssql_execute($query);

echo "<h2>There were $numProds products returned.</h2>";

echo "The lowest priced product was $lowProd.<br>";

echo "The highest priced product was $highProd.";

?>


We start of by defining a number of variables that will be used with our calls to mssql_bind:

$minPrice = 0.00;

$maxPrice = 35.00;

$lowProd = "";

$highProd = "";

$numProds = 0;


[Note] When you are using mssql_bind to setup parameters for stored procedures, you cannot explicitly specify the value for that parameter. You must pass in a variable that contains the value instead. [End Note]

// Bind the parameters

mssql_bind($query, "@minPrice", $minPrice, SQLFLT8);

mssql_bind($query, "@maxPrice", $maxPrice, SQLFLT8);


We then create the input parameters @minPrice and @maxPrice with values taken from the $minPrice amd $maxPrice variables.

mssql_bind($query, "@lowestPricedProduct", &$lowProd, SQLVARCHAR, TRUE, FALSE, 40);

mssql_bind($query, "@highestPricedProduct", &$highProd, SQLVARCHAR, TRUE, FALSE, 40);


For our output parameters, we have specified values for every parameter that the mssql_bind function accepts. Each of our output parameters needs to accept a value back from the stored procedure, so we pass $lowProd and $highProd by reference not value (the ampersand signifies the reference). They will contain the values of the output parameters from the stored procedure after it's executed.

Our parameters will hold the names of products, so we declare them as SQLVARCHAR types. They are output parameters, so we pass in TRUE for the is_output parameter, FALSE for the is_null parameter, and 40 for the maxlen parameter.

In our stored procedure, we return the total number of products whose UnitPrice field is between the values of the @minPrice and @maxPrice input parameters:

RETURN (SELECT COUNT(*) FROM Products WHERE UnitPrice >= @minPrice AND UnitPrice <= @maxPrice)

We access this return value as "RETVAL", returning its value to the $numProds variable, which we pass to mssql_bind by reference:

// Bind the return value

mssql_bind($query, "RETVAL", &$numProds, SQLINT2);


When mssql_execute is called, the stored procedure executes and the values for the output parameters are returned as well as the RETVAL return value. We then output the results to the browser with the echo command:

mssql_execute($query);

echo "<h2>There were $numProds products returned.</h2>";

echo "The lowest priced product was $lowProd.<br>";

echo "The highest priced product was $highProd.";


Here's how prodsbyprice.php looks in my browser:

Running prodsbyprice.php

If you change the values of $minPrice and $maxPrice in prodsbyprice.php, then obviously different products will be returned. When I changed $minPrice to 14.50 and $maxPrice to 350.00, I got the following output:

Running prodsbyprice.php with modifed min and max values

We can also run our stored procedure directly against the Northwind database in query anazlyer with this code:

USE Northwind

GO

DECLARE @lowProd VARCHAR(40)

DECLARE @highProd VARCHAR(40)

EXEC sp_GetNumProdsByPrice 0, 35.50, @lowProd OUTPUT, @highProd OUTPUT

print 'Lowest priced product is ' + @lowProd

print 'Highest priced product is ' + @highProd

blog comments powered by Disqus
PHP ARTICLES

- Removing Singletons in PHP
- Singletons in PHP
- Implement Facebook Javascript SDK with PHP
- Making Usage Statistics in PHP
- Installing PHP under Windows: Further Config...
- File Version Management in PHP
- Statistical View of Data in a Clustered Bar ...
- Creating a Multi-File Upload Script in PHP
- Executing Microsoft SQL Server Stored Proced...
- Code 10x More Efficiently Using Data Access ...
- A Few Tips for Speeding Up PHP Code
- The Modular Web Page
- Quick E-Commerce with PHP and PayPal
- Regression Testing With JMeter
- Building an Iterator with PHP

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials