Working with SQL Server Stored Procedures(Input & Output Parameters) in PHP

Now we will consider a example of changing password proedure:

We have a stored procedure with 4 parameters like email id,old password,new password & one output parameter. If output parameter shows 1 then old password which is in DB is different with typed one. Else if it is 0 then password matches to DB password.

.Net Stored Procedure:

CREATE PROCEDURE [dbo].[SP_some_ChangePassword]
@EmployeeID varchar(50), @OldPassword VARCHAR(200), @NewPassword VARCHAR(200),
@rout INT OUT
AS

IF EXISTS (SELECT * FROM Employees E INNER JOIN EmpCredentails EC ON
EC.EmployeeID = E.EmployeeID INNER JOIN UserRoles UR ON
UR.EmployeeID = E.EmployeeID
WHERE E.UserID = @EmployeeID AND EC.Password = @OldPassword)
BEGIN
Declare @EmpId int
Set @EmpId =(Select EmployeeID from Employees Where UserID = @EmployeeID)

UPDATE EmpCredentails SET Password = @NewPassword WHERE EmployeeID = @EmpId
SET @rout = 1 — Password Changed
END

ELSE
BEGIN
SET @rout = 0 –Old Password is wrong
END

HTML file:

<html>
<head>
</head>
<body>

<form action=”change.php? run=1″ method=”post” border=”0″ style=”padding-bottom:10px”>

<label for=”log” style=”padding-right:6px;”><b> Enter Old Password : </b></label>
<input class=”field” type=”password” name=”op” id=”op” value=”” size=”25″> <br/>
<label for=”log”><b> Enter New Password : </b></label>
<input class=”field” type=”password” name=”np” id=”np” value=”” size=”25″>
<input type=”submit” name=”submit” value=”Submit” class=”button2″>
</form>
</body>
</html>

PHP file:

<?php
header(“Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0”);
header(“Pragma: no-cache”);
$v1 = isset($_REQUEST[‘run’]) ? $_REQUEST[‘run’] : 0 ;
if($v1==1)
{

$a = $_POST[“op”];
$b = $_POST[“np”];

}
$serverName = “serverName\instanceName”; //

$connectionInfo = array(
‘Database’ => ‘DB’,
‘UID’ => ‘userid’,
‘PWD’ => ‘pwd’);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
echo “Connection established.<br />”;
}else{
echo “Connection could not be established.<br />”;
die( print_r( sqlsrv_errors(), true));
}

$id = ‘some@example.com’;
echo $id.'<br/>’;
$rout=1;
$query = “{call SP_some_ChangePassword( ?, ?, ?, ?)}”;
$params = array( array($id, SQLSRV_PARAM_IN),
array($a, SQLSRV_PARAM_IN),
array($b, SQLSRV_PARAM_IN),
array($rout, SQLSRV_PARAM_OUT));
$result1 = sqlsrv_query($conn, $query, $params);
if( $result1 === false )
{
echo “Error in executing statement .\n”;
die( print_r( sqlsrv_errors(), true));
}
if( $rout==0)
echo “password is wrong”;
else
echo “password is right”;
$result2 = sqlsrv_fetch_array($result1);
echo $result2;
echo “Updated”;
?>

It works well for me.. Good Luck 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s